SQL Server String Functions
A string function accepts a string value as an input and returns a string value regardless of the data type (string or numeric). There are several built-in string functions in SQL Server that developers can use to manipulate the character data effectively. String methods in SQL Server are extremely useful for processing the string data type. SQL Server stores string and character data using a variety of data types such as varchar, nvarchar, and char. Therefore, the string functions can be used to obtain the desired and precise results effectively.
The following image will show all string functions used in SQL Server:
This article will cover most of the common string functions used in SQL Server and explain each of them with examples. The following table listed each of the functions with a brief description:
| Function Name | Descriptions |
|---|---|
| ASCII | This function displays the ASCII value of a character. |
| CHAR | This function converts the specified integer code (ASCII) into a single-byte character. |
| CHARINDEX | This function returns the first expression’s starting position if a character expression is found inside a second character expression. |
| CONCAT | This function returns a single string by joining two or more strings. |
| CONCAT_WS | This function concatenates multiple strings into a single and spate them with a separator specified in the first position. |
| DIFFERENCE | This function returns an integer value by comparing the two strings SOUNDEX() values. |
| FORMAT | This function is used to change the text format of the string into any other format. |
| LEFT | This function returns the substring from the left of the string to a specified number of characters. |
| LEN | This function returns the number of characters in a string, including trailing spaces. |
| LOWER | This function is used to convert the upper case character into lower case. |
| LTRIM | This function returns a string from a given string after removing all leading spaces. |
| NCHAR | This function is used to get the Unicode character with the provided integer code based on the UNICODE standard. |
| PATINDEX | This function returns the first occurrence of a pattern in a string’s starting place. If the string is not found, it returns zero. |
| QUOTENAME | This function returns a Unicode string including the delimiters, converting the input string into a valid delimited identifier. |
| REPLACE | This function is used to replace all occurrences of the substring in a specified string with another string value. |
| REPLICATE | This function repeats the string with the specified number of times. |
| REVERSE | This function displays the character string in reverse order. |
| RIGHT | This function returns the substring from the right of the string to a specified number of characters. |
| RTRIM | This function returns a string from a given string after removing all trailing spaces. |
| SOUNDEX | It is used to calculate the similarity of two strings using a four-character (SOUNDEX) code. |
| SPACE | This function is used to finds the string of repeated spaces. |
| STR | This function is used to return the character data converted from numeric data. |
| STRING_AGG | This function concatenates the values of string expressions and inserts separator values in between. It does not add a separator at the end of the string. |
| STRING_ESCAPE | This function escapes special characters in a string and produces a new string containing the characters that were escaped. |
| STRING_SPLIT | It is a table-valued function that divides a string into rows of substrings using a separator of your choice. |
| STUFF | This function removes a portion of a string and replaces it with another substring beginning at a specified position. |
| SUBSTRING | This function extracts a substring from a string that begins at a specific position and ends at a specific length. |
| TRANSLATE | This function combines several one-to-one translations into a single operation. |
| TRIM | This function returns a new string after removing all leading and trailing blanks from a given string. |
| UNICODE | This function returns a character’s integer value as defined by the Unicode standard. |
| UPPER | This function converts the lower case character into the upper case. |
Let us see some of the most common string function examples.
Example1: This example will return the numerical ASCII value of the character ‘A’ and ‘B’:
Executing the statement will return the below result:
Example2: This example will search for the string ‘tutoraspire’ in the ‘Hello tutoraspire’ and, if it finds, return the index of a specific string expression within a given string
Executing the statement will return the below result. Here the first searching for the string tutoraspire returned 7, but when we specified the start location as 8, it returned 0 since no occurrence is found after this index.
Example3: This example will use the CONCAT() function that takes more than two input string and return those string into a single output after concatenation:
Executing the query will display the below output:
Example4: This example uses the SOUNDEX() function that accepts a string as input and produces a four-character string based on how it is spoken. It converts the first character of the code into the upper case and becomes the input string’s first character. The code’s remaining characters are numbers that represent the expression’s letters.
Here is the result:
We can see from the results that the SOUNDEX() method produces the same result for ‘J’ and ‘Ja’ because the character ‘a’ is disregarded (as the A, O, U, E, I, Y, H, W is ignored until they are not the first character). Also, because the SOUNDEX() function only needs the first four characters, the results of tutoraspire and tutoraspire Noida are identical.
Example5: This example uses the DIFFERENCE() function that measures the similarity of the two strings.
This statement gives the below output:
Here, we can see that the DIFFERENCE() function returns a value of 4 as the SOUNDEX() function returns the same value for Hello and Hello World, indicating that they are both based on the SOUNDEX() value. The DIFFERENCE() function, on the other hand, returns 1 for Hello and Bicycle, indicating that they are not similar.
Example6: This example uses the LEFT() and RIGHT() functions to extract a specific number of characters from the left-side or right-side of a string.
Executing the statement will return the below result:
Example7: This example uses the LOWER() and UPPER() to change the character case of an input string. The functions LOWER() and UPPER() are used to modify the letter case to lower case and upper case, respectively.
Executing the statement will return the below result:
Example8: This example will use the LTRIM() and RTRIM() functions to remove additional spaces from an input string’s left or right sides.
Executing the statement will return the below where we see that spaces from the left and right of the string tutoraspire are removed:
Example9: This example uses the QUOTENAME() function that returns a Unicode string including the delimiters, converting the input string into a valid delimited identifier:
Executing the statement will return the below output:
Example10: This example uses the REPLICATE() function to repeat the string a specified number of times.
Executing the statement will return the below result:
Conclusion
This article explains a complete overview of several SQL Server’s built-in string functions, as well as some examples and pictures.