SQL Server String Functions Guide

SQL Server provides a rich set of built-in string functions for manipulating text data. Whether you need to extract substrings, search for patterns, or combine values, these functions handle it all directly in your queries without needing application code.

LEN and DATALENGTH

LEN returns the number of characters in a string, excluding trailing spaces. DATALENGTH returns the number of bytes, which differs for NVARCHAR (2 bytes per character).

SELECT
    LEN('UnifySQL') AS CharCount,         -- 8
    DATALENGTH('UnifySQL') AS ByteCount,  -- 8
    DATALENGTH(N'UnifySQL') AS NByteCount -- 16 (NVARCHAR)

SUBSTRING, LEFT, and RIGHT

Extract portions of a string by position. SUBSTRING takes a start position and length. LEFT and RIGHT grab characters from either end.

SELECT
    SUBSTRING('SQL Server 2022', 5, 6) AS Sub,  -- 'Server'
    LEFT('hello@example.com', 5) AS LeftPart,   -- 'hello'
    RIGHT('order-00123', 5) AS RightPart;        -- '00123'

-- Extract username from email
SELECT
    Email,
    LEFT(Email, CHARINDEX('@', Email) - 1) AS Username
FROM Users;

CHARINDEX and PATINDEX

CHARINDEX finds the position of a substring. PATINDEX finds the position of a pattern using wildcards like % and _.

SELECT
    CHARINDEX('SQL', 'UnifySQL Platform') AS Pos,    -- 6
    PATINDEX('%[0-9]%', 'Order ABC-123') AS NumPos;   -- 11

-- Split a full name
SELECT
    FullName,
    LEFT(FullName, CHARINDEX(' ', FullName) - 1) AS FirstName,
    SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName)) AS LastName
FROM Contacts;

REPLACE and STUFF

REPLACE substitutes all occurrences of a substring. STUFF deletes a portion of a string and inserts a replacement at that position.

SELECT
    REPLACE('2024-01-15', '-', '/') AS Formatted,  -- '2024/01/15'
    STUFF('ABCDEF', 3, 2, 'XYZ') AS Stuffed;       -- 'ABXYZEF'

-- Mask a phone number
SELECT STUFF(PhoneNumber, 1, 6, '***-***') AS MaskedPhone
FROM Customers;

CONCAT and STRING_AGG

CONCAT joins values together, automatically converting NULLs to empty strings. STRING_AGG (SQL Server 2017+) aggregates values from multiple rows into a single delimited string.

-- CONCAT handles NULLs safely
SELECT CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS FullName
FROM Employees;

-- STRING_AGG combines rows into a comma-separated list
SELECT
    Department,
    STRING_AGG(FirstName, ', ') WITHIN GROUP (ORDER BY FirstName) AS Members
FROM Employees
GROUP BY Department;

UPPER, LOWER, and TRIM

These functions handle case conversion and whitespace removal. TRIM was added in SQL Server 2017.

SELECT
    UPPER('unifysql') AS UpperCase,       -- 'UNIFYSQL'
    LOWER('SQL SERVER') AS LowerCase,     -- 'sql server'
    TRIM('  hello  ') AS Trimmed;         -- 'hello'

String functions are essential for data cleaning, formatting output, and building dynamic queries. Combine them to handle complex text transformations directly within your SQL Server queries.