.jpg "SQL String Functions")
What are SQL string functions?
Welcome to InsightNinja, your one stop destination for everything related to ‘Data Analytics’. This is part 6 of ‘SQL for data analytics’ series.
Commonly used SQL String functions:
1. Concatenation(CONCAT or ‘+’ operator)
2. Find Substring(SUBSTRING)
3. String Length(LEN/DATALENGTH )
4. Case Conversion(UPPER/LOWER)
5. Whitespace Removal(LTRIM/RTRIM)
6. Type Casting(CONVERT/CAST)
Let’s get started.
1. Concatenation (`+` operator or `CONCAT` function):
Concatenation is the process of combining two or more strings into one. In SQL Server, you can use the `+` operator to concatenate strings or use the `CONCAT` function, which takes multiple arguments and returns their concatenated result.
Example:
SELECT 'INSIGHT' + ' ' + 'NINJA' AS TITLE
Output:
Similarly we can use CONCAT() function to achieve similar output.
Example:
SELECT CONCAT('INSIGHT',' ','NINJA') AS TITLE
Output:
Let’s see what happens when we use both ‘+’ operator and CONCAT() together.
Example:
SELECT CONCAT('WE'+' '+'LOVE','INSIGHT'+' '+'NINJA') AS TITLE
Output:
Note: CONCAT() functions accepts minimum 2 and maximum 254 arguments. Argument is nothing but comma separated values provided to function.
2. Substring (`SUBSTRING` function):
The `SUBSTRING` function allows you to extract a portion of a string based on a starting position and length.
Example:
SELECT SUBSTRING('WE LOVE INSIGHT
NINJA',2,5) AS [SUB STRING]
Output:
Here, inside substring function first argument should be your main string, second argument should be index of character within that string and third argument should be length of desired string which starts at character’s index provided as second argument.
Note: If my second argument is greater than string length then SQL will return ‘NULL’ in result. In case we provide third argument greater than string length then it will return string till last character of main string.
Let’s play with SUBSTRING and CONCAT function together:
Consider we have two strings ‘WE LOVE INSIGHT NINJA’ and ‘SQL IS BACKBONE OF ANALYTICS’ and we are supposed to extract ‘WE LOVE SQL’ out of it. Here’s how we can achieve this.
SELECT CONCAT(substring('WE LOVE INSIGHT NINJA',1,7),' ',substring('SQL IS BACKBONE OF ANALYTICS',1,3))
The `LEN` function returns the number of characters in a string, while the `DATALENGTH` function returns the number of bytes used to store a string, including trailing spaces.
DECLARE @str char(100)='SQL IS BACKBONE
OF ANALYTICS'
SELECT LEN('SQL IS BACKBONE OF ANALYTICS') AS [LENGTH],DATALENGTH(@str) AS DATA_LENGTH
Output:
Here, We have used a variable @str which holds same string which we have provided to LEN() function. Click here to explore more about variables.
This @str has datatype CHAR which is of size 100 characters. Though @str is only 28 characters long SQL will do padding of remaining 72 characters with whitespaces. Click here to explore more about Datatypes.
3. String Case Conversion (`UPPER`, `LOWER`):
SQL Server provides functions to convert strings to uppercase (`UPPER`), lowercase (`LOWER`)
Example:
SELECT LOWER('SQL IS BACKBONE
OF ANALYTICS') [Lower String]
,UPPER('sql is BACKBONE of ANALYTICS') [Upper String]
What if we want to capitalize first letter of each word, Database management systems like oracle provides inbuilt function called ‘INITCAP’ that serves the purpose. But no such function is available in SQL.
Here’s one of the ways to achieve the same in SQL server.
declare @str varchar(100)='sql is backbone
of analytics'
declare @result varchar(100)=''
declare @current int =1
declare @prev char(10)=' '
while @current<=len(@str)
begin
if @current=1 or @prev=' '
begin
set @result+= upper(substring(@str,@current,1))
end
else
begin
set @result+= lower(substring(@str,@current,1))
end
set @prev=substring(@str,@current,1)
set @current+=1
end
Output:
4. Replacing Substrings (`REPLACE` function):
The `REPLACE` function allows you to replace occurrences of a substring with another substring within a string.
Example:
SELECT REPLACE('SQL IS BACKBONE OF ANALYTICS','s','a') AS [Replaced String]
Output:
Here, First argument should be original string, second argument should be target sub string and third should be replacement sub string. In our case we wanted to replace all occurrences of ‘s’ by ‘a’.
Suppose you have a date in string format ‘2030-01-01’ and you want it in ‘20230101’ format. Here’s how we can achieve this using REPLACE() function.
Example:
SELECT REPLACE('2030-01-01','-','') AS [Replaced String]
Output:
5. Trimming (`LTRIM`, `RTRIM`)
Trimming functions are used to remove leading spaces (`LTRIM`) or trailing spaces (`RTRIM`).
Example:
SELECT LTRIM(' SQL IS BACKBONE OF ANALYTICS ') AS [Replaced String]
Output:
SELECT RTRIM(' SQL IS BACKBONE OF ANALYTICS ') AS [Replaced String]
Output:
In case you want to remove leading and trailing spaces together, here’s what you can do.
Example:
SELECT LTRIM(RTRIM(' SQL IS BACKBONE OF ANALYTICS ')) AS [Replaced String]
Output:
6 . String Conversion (`CAST` or `CONVERT` functions):
Syntax:
CAST: CAST(expression AS data_type)
CONVERT: CONVERT(data_type, expression, style)
Both these functions help us to convert value from one datatype to other provided they are compatible with each other.
Let’s try to understand with examples.
SELECT CONVERT(varchar(10),9.88) as [CONVERTED VALUE]
Output:
In above example we have converted a numeric value 9.88 to varchar(10).
DECLARE @float FLOAT=9.88
SELECT CONVERT(INT,@float) AS [CONVERTED
VALUE]
Output:
In above example we have converted a float value 9.88 to integer value. Still we have not used third argument in CONVERT() function which is ‘STYLE’. Let’s try to understand significance of this argument with date and time examples.
Consider we want to convert a date value to string value, here’s how we can achieve it.
DECLARE @date DATE='2030-01-01'
SELECT CONVERT(VARCHAR(10),@date) as [CONVERTED
VALUE]
Output:
Now let’s insert 3rd argument to this example.
DECLARE @date DATE='2030-01-01'
SELECT CONVERT(VARCHAR(10),@date,112) as [CONVERTED
VALUE]
Output:
Here, we have used style ‘112’ to make our date into string without ‘-’ symbol.
DECLARE @date DATE='2030-01-01'
SELECT CONVERT(VARCHAR(15),@date,106) as [CONVERTED
VALUE]
Output:
Here’s list of such styles:
|
Style Code |
Description |
Example Output |
|
1 |
MM/DD/YY |
08-05-2023 |
|
101 |
MM/DD/YYYY |
08-05-2023 |
|
2 |
YY.MM.DD |
23.08.05 |
|
102 |
YYYY.MM.DD |
2023.08.05 |
|
3 |
DD/MM/YY |
05-08-2023 |
|
103 |
DD/MM/YYYY |
05-08-2023 |
|
4 |
DD.MM.YY |
05.08.23 |
|
104 |
DD.MM.YYYY |
05.08.2023 |
|
5 |
DD-MM-YY |
05-08-2023 |
|
105 |
DD-MM-YYYY |
05-08-2023 |
|
6 |
DD Mon YY |
05-Aug-23 |
|
106 |
DD Mon YYYY |
05-Aug-23 |
|
7 |
Mon DD, YY |
Aug 05, 23 |
|
107 |
Mon DD, YYYY |
Aug 05, 2023 |
|
8 |
HH:MI:SS |
13:45:22 |
|
9 |
HH:MI:SS:mmm |
45:22.1 |
|
10 |
MM-DD-YY |
08-05-2023 |
|
110 |
MM-DD-YYYY |
08-05-2023 |
|
11 |
YY/MM/DD |
23-08-2005 |
|
111 |
YYYY/MM/DD |
05-08-2023 |
|
12 |
YYMMDD |
230805 |
|
13 |
DD Mon YY HH:MI:SS |
05-08-2023 13:45 |
|
113 |
DD Mon YYYY HH:MI:SS |
05-08-2023 13:45 |
|
14 |
Mon DD, YY HH:MI:SS |
Aug 05, 23 13:45:22 |
|
114 |
Mon DD, YYYY HH:MI:SS |
Aug 05, 2023 13:45:22 |
|
20 |
YYYY-MM-DD HH:MI:SS |
05-08-2023 13:45 |
|
120 |
YYYY-MM-DD HH:MI:SS |
05-08-2023 13:45 |
|
21 |
YYYY-MM-DD HH:MI:SS.mmm |
45:22.1 |
|
121 |
YYYY-MM-DD HH:MI:SS.mmm |
45:22.1 |
|
126 |
ISO8601 - YYYY-MM-DDTHH:MI:SS.mmm (without space) |
2023-08-05T13:45:22.123 |
|
127 |
ISO8601 with time zone offset |
2023-08-05T13:45:22.123Z |
|
100 |
Mon DD YYYY HH:MIAM (or PM) |
Aug 05 2023 01:45PM |
|
130 |
DD Mon YYYY HH:MI:SS:mmmAM (or PM) |
05 Aug 2023 01:45:22.123PM |
CAST() function is quite similar to CONVERT() with difference being we can not use style argument here,
Considering last example where we have converted a float value to int. now we will try to convert integer value to float datatype using CAST().
Example:
DECLARE @int INT=9
SELECT CAST(@int as float) as [CONVERTED VALUE]
Output:
Less Frequently used SQL String functions:
1. CHARINDEX:
It Returns the starting position of a first substring within a string.
Example:
SELECT CHARINDEX('WE','WE LOVE WE
NINJA',1)
Output:
Here, We wanted our function to find ‘WE’ in main string with starting position as 1. So it started searching from 1st index, SQL found ‘WE’ at location ‘12’ and as it returns starting location, We got 1 in result.
2. PATINDEX:
Returns the starting position of a pattern within a string using a wildcard pattern.
SELECT PATINDEX('%LOVE INS%', 'WE LOVE INSIGHT NINJA')
Output:
3. STUFF:
It replaces a specified length of characters in a string with another set of characters.
SELECT STUFF('WE LOVE INSIGHT
NINJA',
9,
7,
'SQL')
Output:
4. REVERSE:
It reverses the characters in a string.
SELECT REVERSE('WE LOVE INSIGHT
NINJA')
Output:
5. ASCII:
It returns ascii code value to character.
SELECT ASCII('A') AS ASCII_A, ASCII('a') AS ASCII_a
Output:
6. CHAR:
It returns character value of mentioned ASCII code.
SELECT CHAR('65') AS CHAR_65, CHAR('97') AS CHAR_97
Output:
.jpg&description=Cracking the code with SQL String Functions){kind=link}
0 Comments