In this article, we will try to give a brief overview of the SQL string functions used in SQL Server and we will provide some examples.
A string function is a function that takes a string value as an input regardless of the data type of the returned value. In SQL Server, there are many built-in string functions that can be used by developers.
ASCII
The first SQL string function we will describe is ASCII(), which is a scalar function that takes a string as input and returns the ASCII code of the first character in this string. Note that ASCII stands for American Standard Code for Information Interchange. It’s a 7-bit character code where every single bit represents a unique character which can be used for different purposes. You can find the whole ASCII table in the following website: ASCII Code – The extended ASCII table.
Example:
|
1 |
SELECT ASCII('A'), ASCII('AB') , ASCII('B') |
Result:
65, 65, 66
As shown in the result ASCII(‘A’) and ASCII(‘AB’) return the same result 65
CHARINDEX
CHARINDEX() is a scalar SQL string function used to return the index of a specific string expression within a given string. CHARINDEX() has 2 required parameters which are the input string and character and one optional parameter which is the starting index of the search operation (If this argument is not specified or is less or equal than zero (0) value, the search starts at the beginning of input string).
The function return type depends on the input string length; if it is NVARCHAR(MAX) then it will return a BIGINT value else it will return an INT value.
Example:
|
1 |
SELECT CHARINDEX('World','Hello World'),CHARINDEX('World','Hello World',8) |
Result:
7, 0
As shown in the example above, we searched for the string World within Hello World and it returned 7, but when we specified the start location as 8, it returned 0 since no occurrence is found after this index.
CONCAT
CONCAT() is a scalar SQL string function that takes multiple strings as input and returns on the string after concatenating all inputs. This function can take a maximum 254 of inputs.
Example:
|
1 |
SELECT CONCAT('Hello',' World') |
Result:
Hello World
CONCAT_WS
CONCAT_WS() is very similar to CONCAT() function, but it allows the user to specify a separator between the concatenated input strings. It can be used to generate comma-separated values.
Example:
|
1 |
SELECT CONCAT_WS(',','United States','New York') |
Result:
United States, New York
SOUNDEX
SOUNDEX() is a scalar function that takes a string value as input and returns a four-character string based on the way this string is spoken. The first character of the code is the first character of the input string, converted to upper case. The remaining characters of the code are numbers that represent the letters in the expression. Note that there are some letters that are ignored (A,O,U,E,I,Y,H,W) except if they are the first letter. Also, if the string length is less than 4 then additional zeros are added to the returned value.
SOUNDEX() is mainly used for string matching and row linkage purposes.
Example:
|
1 |
SELECT SOUNDEX('H'), SOUNDEX('He'), SOUNDEX('Hello'), SOUNDEX('Hello World') |
Result:
H000, H000, H400, H400
From the results above, we can see that the result of the SOUNDEX() function is the same for ‘H’ and ‘He’ since the letter ‘e’ is ignored (as mentioned above). Also, the result of Hello and Hello World is the same since the SOUNDEX() function takes only the first 4 characters.
DIFFERENCE
DIFFERENCE() is a scalar function used to measure the similarity of two strings using another SQL string function, which is SOUNDEX(). First, SOUNEDX() is applied to each input and then a similarity check is done over these results. This function returns an integer value between 0 and 4. When this value is closer to 4, then inputs are very similar.
Example:
|
1 |
SELECT DIFFERENCE('HELLO','BICYCLE'), DIFFERENCE('HELLO', 'HELLO WORLD') |
Result:
1, 4
From the results above, since the SOUNDEX() function returns the same value for HELLO and HELLO WORLD, then the result of the DIFFERENCE() function is 4 which implies that they are very similar (based on SOUNDEX()). On the other hand, the result of the DIFFERENCE() function for HELLO and BICYCLE is 1 which implies they are not similar.
LEFT, RIGHT
LEFT() and RIGHT() functions are one of the most popular SQL string functions. They are used to extract a specific number of characters from the left-side or right-side of a string.
Example:
|
1 |
SELECT LEFT('Hello World',5) , RIGHT('Hello Wolrd',5) |
Result:
Hello, World
LOWER, UPPER
LOWER() and UPPER() functions are another popular SQL string functions that are used to change the character case of an input string. LOWER() is used to change the letter case to a lower case and UPPER() is used to change the case of the letters into upper case.
Example:
|
1 |
SELECT LOWER('Hello World') , UPPER('Hello World') |
Result:
hello world, HELLO WORLD
LTRIM, RTRIM
The last functions we will illustrate in this article are LTRIM() and RTRIM() function, which are used to remove additional spaces from the left side or right side of an input string.
Example:
|
1 |
SELECT RTRIM('Hello ') , LTRIM(' World') |
Result:
Hello, World
Conclusion
In this article, we given an overview of some of the built-in SQL string functions in SQL Server, we provided some example and screenshots and we briefly discussed the results obtained.
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023