Transact-SQL (T-SQL) includes a number of scalar functions that allow us to perform operation on strings when working with SQL Server. These functions accept an input string, and return either a string or a numeric value.
The following is a list of T-SQL string functions available in SQL Server. Click on each function or operator name to see an explanation of the function, its syntax, and examples.
| Function | Description |
|---|---|
ASCII() | Returns the ASCII code value of the leftmost character of a character expression. |
CHAR() | Converts an int ASCII code to a character value and returns the result. |
CHARINDEX() | Searches for one character expression inside a second character expression, returning the starting position of the first expression if found. |
CONCAT() | Returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner. |
CONCAT_WS() | Returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner, and separating those concatenated string values with the delimiter specified in the first function argument. |
DIFFERENCE() | Returns an integer value measuring the difference between the SOUNDEX() values of two different character expressions. |
FORMAT() | Returns a value formatted with the specified format and optional culture. |
LEFT() | Returns the left part of a character string with the specified number of characters. |
LEN() | Returns the number of characters of the specified string expression, excluding trailing blanks. |
LOWER() | Returns a character expression after converting uppercase character data to lowercase. |
LTRIM() | Returns a character expression after it removes leading blanks. |
NCHAR() | Returns the Unicode character based on the specified integer code. |
PATINDEX() | Returns the starting position of the first occurrence of a pattern in a specified expression. |
QUOTENAME() | Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. |
REPLACE() | Replaces all instances of a given string with another string. |
REPLICATE() | Repeats a string value a specified number of times, and returns the result. |
REVERSE() | Returns a string value in reverse order. |
RIGHT() | Returns the right part of a character string with the specified number of characters. |
RTRIM() | Removes trailing blanks from a given string. |
SOUNDEX() | Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. |
SPACE() | Returns a string of repeated spaces. |
STR() | Returns character data converted from numeric data. |
STRING_AGG() | Concatenates the values of string expressions and places separator values between them. |
STRING_ESCAPE() | Escapes special characters in texts and returns text with escaped characters. |
STRING_SPLIT() | Splits a string into rows of substrings, based on a specified separator character. |
STUFF() | Inserts a string into another string. |
SUBSTRING() | Returns part of a character, binary, text, or image expression. |
TRANSLATE() | Returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters specified in the third argument. |
TRIM() | Removes the space character char(32) or other specified characters from the start or end of a string. |
UNICODE() | Returns the Unicode value of a given character. |
UPPER() | Returns a character expression with lowercase character data converted to uppercase. |
If the input string passed to a string function is not a string value, it is implicitly converted to a text data type.
Also, all built-in string functions except FORMAT are deterministic. This means they return the same value any time they are called with a specific set of input values.