SQL String Functions

String functions are used to manipulate and process character string data in SQL Server. They allow you to perform various operations such as concatenating strings, extracting substrings, changing case, finding lengths, and more.

Common String Functions

1. CONCAT()

Concatenates two or more string values. Returns the concatenated string. If any argument is NULL, it is treated as an empty string.

SELECT CONCAT('SQL ', 'Server ', 'is ', 'great!');
-- Result: SQL Server is great!

SELECT CONCAT('MSDN', NULL, ' Docs');
-- Result: MSDN Docs

2. LENGTH() / LEN()

Returns the number of characters in a string expression. `LEN()` excludes trailing blanks, while `LENGTH()` includes them.

SELECT LEN('Hello World');
-- Result: 11

SELECT LENGTH('SQL ');
-- Result: 4

3. SUBSTRING() / SUBSTR()

Extracts a part of a string. It takes the string, a starting position, and the number of characters to extract.

SELECT SUBSTRING('Microsoft SQL Server', 11, 6);
-- Result: SQL Ser

SELECT SUBSTR('Database', 5, 3);
-- Result: bas

4. UPPER() and LOWER()

Converts a string to all uppercase or all lowercase characters.

SELECT UPPER('sql server');
-- Result: SQL SERVER

SELECT LOWER('SQL SERVER');
-- Result: sql server

5. REPLACE()

Replaces all occurrences of a specified substring with another substring within a string.

SELECT REPLACE('Welcome to the SQL world!', 'SQL', 'MSDN');
-- Result: Welcome to the MSDN world!

6. LEFT() and RIGHT()

Returns the specified number of characters from the left or right side of a string.

SELECT LEFT('SQL Server', 3);
-- Result: SQL

SELECT RIGHT('Documentation', 5);
-- Result: tion

7. TRIM(), LTRIM(), RTRIM()

Removes leading, trailing, or both leading and trailing spaces (or specified characters) from a string.

SELECT TRIM('   Trim Me   ');
-- Result: Trim Me

SELECT LTRIM('   Leading Spaces');
-- Result: Leading Spaces

SELECT RTRIM('Trailing Spaces   ');
-- Result: Trailing Spaces

8. CHARINDEX() / PATINDEX()

CHARINDEX() returns the starting position of the first occurrence of a substring within a string. PATINDEX() returns the starting position of the first occurrence of a pattern within a string, supporting wildcard characters.

SELECT CHARINDEX('Server', 'SQL Server');
-- Result: 5

SELECT PATINDEX('%[0-9]%', 'Document123');
-- Result: 9

Examples with Tables

Let's assume we have a table named Products with columns ProductName and ProductCode.

Concatenating Product Name and Code

SELECT CONCAT(ProductName, ' (', ProductCode, ')') AS FullProductInfo
FROM Products;

Extracting First 5 Characters of Product Name

SELECT LEFT(ProductName, 5) AS ShortName
FROM Products;

Finding the position of a specific word

SELECT ProductName,
       CHARINDEX('Pro', ProductName) AS PositionOfPro
FROM Products
WHERE CHARINDEX('Pro', ProductName) > 0;

Further Reading

• SQL Aggregate Functions
• SQL Scalar Functions
• SQL Date and Time Functions