SQL String Functions
This section details the built-in string functions available in SQL Server. These functions allow you to manipulate and process character data effectively.
String Manipulation Functions
LEN() / DATALENGTH()
LEN(string_expression)
DATALENGTH(string_expression)
Returns the number of characters in a string expression, excluding trailing blanks. DATALENGTH returns the number of bytes used to represent the expression.
Example:
SELECT LEN(' Hello World ') AS CharLength,
DATALENGTH(' Hello World ') AS ByteLength;
-- Output: CharLength = 11, ByteLength = 22 (for NVARCHAR) or 11 (for VARCHAR)
SUBSTRING() / SUBSTR()
SUBSTRING(string_expression, start, length)
Extracts a portion of a string. start is the starting position (1-based), and length is the number of characters to extract.
Example:
SELECT SUBSTRING('SQL Server', 5, 6);
-- Output: Server
LEFT()
LEFT(string_expression, number_of_characters)
Returns the left-most part of a string with the specified number of characters.
Example:
SELECT LEFT('Microsoft SQL', 9);
-- Output: Microsoft
RIGHT()
RIGHT(string_expression, number_of_characters)
Returns the right-most part of a string with the specified number of characters.
Example:
SELECT RIGHT('Microsoft SQL', 3);
-- Output: SQL
REPLACE()
REPLACE(string_expression, string_to_replace, replacement_string)
Replaces all occurrences of a specified string within another string.
Example:
SELECT REPLACE('This is a test string.', 'is', 'IS');
-- Output: ThIS IS a test string.
REVERSE()
REVERSE(string_expression)
Reverses the order of characters in a string.
Example:
SELECT REVERSE('abcde');
-- Output: edcba
String Formatting and Case Functions
UPPER()
UPPER(string_expression)
Converts a string to uppercase.
Example:
SELECT UPPER('sql server');
-- Output: SQL SERVER
LOWER()
LOWER(string_expression)
Converts a string to lowercase.
Example:
SELECT LOWER('SQL SERVER');
-- Output: sql server
LTRIM()
LTRIM(character_expression)
Removes leading spaces from a string.
Example:
SELECT LTRIM(' Trimmed Left');
-- Output: Trimmed Left
RTRIM()
RTRIM(character_expression)
Removes trailing spaces from a string.
Example:
SELECT RTRIM('Trimmed Right ');
-- Output: Trimmed Right
TRIM()
TRIM( [ [ LEADING | TRAILING | BOTH ] [ characters ] FROM ] string_expression )
Removes leading, trailing, or both leading and trailing specified characters (or spaces by default) from a string.
Example:
SELECT TRIM('---String---'); -- Removes both leading/trailing '-'
-- Output: String
SELECT TRIM(LEADING '-' FROM '---String---'); -- Removes leading '-'
-- Output: String---
SELECT TRIM(TRAILING '-' FROM '---String---'); -- Removes trailing '-'
-- Output: ---String
SELECT TRIM(BOTH '-' FROM '---String---'); -- Same as default
-- Output: String
String Searching Functions
CHARINDEX()
CHARINDEX(substring, string_expression [, start_location])
Returns the starting position of the first occurrence of a substring within a string. start_location is optional.
Example:
SELECT CHARINDEX('Server', 'SQL Server Database');
-- Output: 5
PATINDEX()
PATINDEX('%pattern%', string_expression)
Returns the starting position of the first occurrence of a pattern in a string, using wildcard characters.
Example:
SELECT PATINDEX('%[^A-Z]%', 'SQLServer'); -- Find first non-uppercase char
-- Output: 4 (position of 'S' after 'L')
SELECT PATINDEX('%[0-9]%', 'User123'); -- Find first digit
-- Output: 5