Hadi Fadlallah

An overview of SQL String Functions

December 25, 2019 by

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:

Result:

65, 65, 66

Example of ASCII sql string function

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:

Result:

7, 0

Example of CHARINDEX sql string function

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:

Result:

Hello World

example of CONCAT sql string function

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:

Result:

United States, New York

example of CONCAT_WS sql string function

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:

Result:

H000, H000, H400, H400

example of SOUNDEX function

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:

Result:

1, 4

example of difference() function

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:

Result:

Hello, World

example of LEFT and RIGHT sql string functions

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:

Result:

hello world, HELLO WORLD

example of LOWER and Upper string functions

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:

Result:

Hello, World

example of RTRIM and LTRIM functions

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.

Hadi Fadlallah
350 Views