• Article

Using String Functions (Database Engine)

String functions are used for various operations on character and binary strings, and they return values generally required for operations on character data. Most string functions can be used only on char, nchar, varchar, and nvarchar data types or the data types that implicitly convert to them. Some string functions can also be used on binary and varbinary data.

You can use string functions to do the following:

  • Retrieve only part of a string (SUBSTRING).

  • Search for similarities in the sounds of a character string (SOUNDEX and DIFFERENCE).

  • Find a starting position for a particular string in a column or expression. For example, the position number of the letter A in "What a beautiful day!"

  • Concatenate strings into one string. For example, combining a first name, last name, and middle name or initial into a full name.

  • Convert a nonstring value to a string value, such as converting the value of 15.7 stored as a float to char.

  • Inserting a specific string into an existing string. For example, inserting the string "Once" into the existing string of "upon a time" to produce the string "Once upon a time".

When string functions operate on strings that have different collations, the collation of the result is determined by using the rules of collation precedence. For more information, see Collation Precedence (Transact-SQL).