String functions perform various operations on binary data, character data, and expressions. String functions can be used anywhere an expression is allowed to return values commonly needed for operations on character data and string concatenation. Various system functions, such as COL_LENGTH and DATALENGTH, can also prove useful. For details, see "System Functions," later in this topic.
With the exception of the PATINDEX function, string functions can be used only on char, varchar, binary, and varbinary datatypes, plus those datatypes that convert implicitly to char or varchar. The PATINDEX function can be used on char, varchar, and text datatypes. For functions specifically designed to manipulate text and image data, see the text and image Manipulation topic.
function_name(parameters)
where
These are the string functions:
| Function | Parameters | Result |
|---|---|---|
| (expression expression) | Concatenates two character strings, binary strings, column names, or a combination of them. Enclose character strings in quotation marks. When concatenating non-character, non-binary expressions, always use the CONVERT function. | |
| ASCII | (char_expr) | Indicates the ASCII code value of the leftmost character of a character expression. |
| CHAR | (integer_expr) | Converts a character from an ASCII code. The ASCII code should be a value from 0 through 255; otherwise, NULL is returned. |
| CHARINDEX | ('pattern', expression) | Returns the starting position of the specified pattern. A pattern is a char_expr. The second parameter is an expression, usually a column name, in which SQL Server searches for the pattern. |
| DIFFERENCE | (char_expr1, char_expr2) | Shows the difference between the values of two character expressions as returned by the SOUNDEX function. The DIFFERENCE function compares two strings and evaluates the similarity between them, returning a value from 0 through 4. The value 4 is the best match, meaning that the SOUNDEX values of char_expr1 and char_expr2 are identical. If the first character of SOUNDEX(char_expr1) is identical to the first character of SOUNDEX(char_expr2), then the starting DIFFERENCE value is 1; otherwise, it is 0. Then each numeric character of char_expr2 is compared to the numeric characters of char_expr1, adding 1 to the DIFFERENCE value if a match is found. |
| LOWER | (char_expr) | Converts uppercase character data to lowercase. |
| LTRIM | (char_expr) | Removes leading blanks. |
| PATINDEX | ('%pattern%', expression) | Returns the starting position of the first occurrence of pattern in the specified expression, or zeros if the pattern is not found. You can use wildcard characters in pattern, as long as the wildcard character % precedes and follows pattern (except when searching for first or last characters). The expression is usually a column name. You can use this function on text, char, and varchar data. |
| REPLICATE | (char_expr, integer_expr) | Repeats a character expression a specified number of times. If integer_expr is negative, a null string is returned. |
| REVERSE | (char_expr) | Returns the reverse of char_expr. This function takes a constant, variable, or column as its parameter. |
| RIGHT | (char_expr, integer_expr) | Part of a character string starting integer_expr characters from the right. If integer_expr is negative, a null string is returned. |
| RTRIM | (char_expr) | Removes trailing blanks. |
| SOUNDEX | (char_expr) | Returns a four-digit (SOUNDEX) code to evaluate the similarity of two strings. The SOUNDEX function converts an alpha string to a four-digit code to find similar-sounding words or names. The first character of the code is the first character of char_expr and the second through fourth characters of the code are numbers. Vowels in char_expr are ignored unless they are the first letter of the string. |
| SPACE | (integer_expr) | Returns a string of repeated spaces. The number of spaces is equal to integer_expr. If integer_expr is negative, a null string is returned. |
| STR | (float_expr [, length [, decimal]]) |
Returns character data converted from numeric data. The length is the total length, including decimal point, sign, digits, and spaces. The decimal value is the number of spaces to the right of the decimal point. Both length and decimal are optional. The default for length is 10. Do not use a function or subquery as the float_expr in the STR function. A short float_expr is right-justified in the specified length, and a long float_expr is truncated to the specified number of decimal places. |
| STUFF | (char_expr1, start, length, char_expr2) | Deletes length characters from char_expr1 at start and then inserts char_expr2 into char_expr1 at start. If the start position or the length is negative, a null string is returned. If the start position is longer than char_expr1, a null string is returned. If the length to delete is longer than char_expr1, it is deleted to the first character in char_expr1. |
| SUBSTRING | (expression, start, length) | Returns part of a character or binary string. The first parameter can be a character or binary string, a column name, or an expression that includes a column name. (Do not use expressions that include functions.) The second parameter specifies where the substring begins. The third parameter specifies the number of characters in the substring. |
| UPPER | (char_expr) | Converts lowercase character data to uppercase. |
where
String functions can be nested.
Creating one result from multiple character columns can be achieved through string concatenation. This example creates a single column (under the column heading Name) in order of last name followed by first name with a comma after the last name in the authors table:
SELECT Name = (au_lname + ', ' + au_fname)
FROM authors
This example shows how to return only a portion of a character string. From the authors table, this query will return the last name in one column with only the first initial in the second column.
SELECT au_lname, SUBSTRING(au_fname, 1, 1)
FROM authors
This example creates a single column (under the column heading Name) in order of last name, first initial with a comma after the last name in the authors table.
SELECT Name = (au_lname + ', ' + SUBSTRING(au_fname, 1, 1) + '.')
FROM authors
This example displays the first six characters of the concatenation of each pub_id and title_id. It displays all four characters of the pub_id and the first two characters of the title_id.
SELECT SUBSTRING ((pub_id + title_id), 1, 6)
FROM titles
This example returns the position at which the pattern 'wonderful' begins in the notes column of titles:
SELECT CHARINDEX('wonderful', notes)
FROM titles
WHERE title_id = 'TC3218'
This example returns a character string created by deleting three characters from the first string ('abcdef') starting at position 2 (at 'b') and inserts the second string at the deletion point. The resulting string is 'aijklmnef':
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
This example shows the SOUNDEX function and the related DIFFERENCE function. In the first example, the standard SOUNDEX values are returned for all consonants. Returning the SOUNDEX for 'Smith' and 'Smythe' will return the same SOUNDEX result because all vowels (and y) are not included.
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')
----- |
----- |
S530 |
S530 |
The DIFFERENCE function compares the difference of the SOUNDEX pattern results. The first example shows two strings that differ only in vowels. The difference returned will be 4 (lowest possible difference).
SELECT DIFFERENCE('Smithers', 'Smothers')
In this example, the strings differ in consonants, so the difference returned will be 2 (higher difference).
SELECT DIFFERENCE('Smothers', 'Brothers')