String functions are used to perform an operation on input string and return an output string³. SQL Server has many built-in string functions that can be used to manipulate or transform string data¹.

Some of the common string functions in SQL Server are:

• ASCII: Returns the ASCII value for the specific character. For example, SELECT ASCII('A') returns 65.

• CHAR: Returns the character based on the ASCII code. For example, SELECT CHAR(65) returns A.

• CHARINDEX: Returns the position of a substring in a string. For example, SELECT CHARINDEX('SQL', 'SQL Server') returns 1.

• CONCAT: Adds two or more strings together. For example, SELECT CONCAT('Hello', ' ', 'World') returns Hello World.

• CONCAT_WS: Adds two or more strings together with a separator. For example, SELECT CONCAT_WS('-', '2021', '01', '01') returns 2021-01-01.

• DATALENGTH: Returns the number of bytes used to represent an expression. For example, SELECT DATALENGTH('Hello') returns 5.

• DIFFERENCE: Compares two SOUNDEX values, and returns an integer value. For example, SELECT DIFFERENCE('Smith', 'Smyth') returns 4.

• FORMAT: Formats a value with the specified format. For example, SELECT FORMAT(1234.5678, 'N2') returns 1,234.57.

• LEFT: Extracts a number of characters from a string (starting from left). For example, SELECT LEFT('Hello World', 5) returns Hello.

• LEN: Returns the length of a string. For example, SELECT LEN('Hello World') returns 11.

• LOWER: Converts a string to lower-case. For example, SELECT LOWER('HELLO WORLD') returns hello world.

• LTRIM: Removes leading spaces from a string. For example, SELECT LTRIM(' Hello World') returns Hello World.

• NCHAR: Returns the Unicode character based on the number code. For example, SELECT NCHAR(65) returns A.

• PATINDEX: Returns the position of a pattern in a string. For example, SELECT PATINDEX('%[0-9]%', 'SQL2019') returns 4.

• QUOTENAME: Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier. For example, SELECT QUOTENAME('Hello World') returns [Hello World].

• REPLACE: Replaces all occurrences of a substring within a string, with a new substring. For example, SELECT REPLACE('Hello World', 'World', 'SQL') returns Hello SQL.

• REPLICATE: Repeats a string a specified number of times. For example, SELECT REPLICATE('Hello', 3) returns HelloHelloHello.

• REVERSE: Reverses a string and returns the result. For example, SELECT REVERSE('Hello World') returns dlroW olleH.

• RIGHT: Extracts a number of characters from a string (starting from right). For example, SELECT RIGHT('Hello World', 5) returns World.

• RTRIM: Removes trailing spaces from a string. For example, SELECT RTRIM('Hello World ') returns Hello World.

• SOUNDEX: Returns a four-character code to evaluate the similarity of two strings. For example, SELECT SOUNDEX('Smith') returns S530.

• SPACE: Returns a string of the specified number of space characters. For example, SELECT SPACE(5) returns .

• STR: Returns a number as string. For example, SELECT STR(123.456, 6, 2) returns 123.46.

• STUFF: Deletes a part of a string and then inserts another part into the string, starting at a specified position. For example, SELECT STUFF('Hello World', 7, 5, 'SQL') returns Hello SQL.

• SUBSTRING: Extracts some characters from a string. For example, SELECT SUBSTRING('Hello World', 7, 5) returns World.

• TRANSLATE: Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument. For example, SELECT TRANSLATE('SQL Server', 'er', 'xy') returns SQL Sxvy.

• TRIM: Removes leading and trailing spaces (or other specified characters) from a string. For example, SELECT TRIM(' Hello World ') returns Hello World.

• UNICODE: Returns the Unicode value for the first character of the input expression. For example, SELECT UNICODE('A') returns 65.

• UPPER: Converts a string to upper-case. For example, SELECT UPPER('hello world') returns HELLO WORLD.