Overview
Scalar string functions operate on character data and return a single value. Use them to manipulate, query, and transform string expressions.
| Function | Syntax | Description | Example |
|---|---|---|---|
LEN |
LEN ( string_expression ) |
Returns the number of characters of the specified string, excluding trailing blanks. | SELECT LEN('Hello World'); -- 11 |
LEFT |
LEFT ( character_expression , integer_expression ) |
Returns the left part of a character expression with the specified number of characters. | SELECT LEFT('Adventure', 4); -- 'Adve' |
RIGHT |
RIGHT ( character_expression , integer_expression ) |
Returns the right part of a character expression with the specified number of characters. | SELECT RIGHT('Adventure', 4); -- 'ture' |
SUBSTRING |
SUBSTRING ( expression , start , length ) |
Returns part of a character, binary, text, or image expression. | SELECT SUBSTRING('Adventure', 2, 5); -- 'dvent' |
REPLACE |
REPLACE ( string_expression , string_pattern , string_replacement ) |
Replaces all occurrences of a specified string value with another string value. | SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL' |
UPPER |
UPPER ( string_expression ) |
Returns a character expression converted to uppercase. | SELECT UPPER('sql'); -- 'SQL' |
LOWER |
LOWER ( string_expression ) |
Returns a character expression converted to lowercase. | SELECT LOWER('SQL'); -- 'sql' |
LTRIM |
LTRIM ( string_expression ) |
Removes leading spaces from a string. | SELECT LTRIM(' data'); -- 'data' |
RTRIM |
RTRIM ( string_expression ) |
Removes trailing spaces from a string. | SELECT RTRIM('data '); -- 'data' |
REVERSE |
REVERSE ( string_expression ) |
Returns the reverse order of characters in a string. | SELECT REVERSE('SQL'); -- 'LQS' |
CHARINDEX |
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] ) |
Returns the starting position of the specified expression in a character string. | SELECT CHARINDEX('SQL','Microsoft SQL Server'); -- 11 |