SQL Server String Functions

T-SQL's string toolbox is complete but full of version boundaries: STRING_SPLIT arrived in 2016, CONCAT_WS, TRIM, and STRING_AGG in 2017, GREATEST/LEAST in 2022, and real regular expressions only in SQL Server 2025. This guide covers the functions you'll actually use, the NULL and truncation traps, and which version you need for each.

Concatenation: + vs CONCAT

The + operator concatenates strings, but any NULL operand makes the entire result NULL:

SELECT 'Hello' + NULL + 'World';   -- NULL
SELECT CONCAT('Hello', NULL, 'World');  -- 'HelloWorld'

CONCAT (SQL Server 2012+) treats NULL as an empty string and implicitly converts non-string arguments. This is the single most common source of unexpectedly-NULL computed columns: a first_name + ' ' + middle_name + ' ' + last_name expression goes NULL for anyone without a middle name.

CONCAT_WS (2017+) adds a separator and skips NULLs entirely -- useful for building addresses or CSV lines:

SELECT CONCAT_WS(', ', street, unit, city, postal_code)
FROM addresses;
-- unit is NULL → no doubled separator

Note the difference: CONCAT turns NULL into '' (a CONCAT_WS with a NULL middle value would still produce a clean list, while CONCAT with manual separators leaves you with 'a, , b').

LEN vs DATALENGTH

LEN returns the number of characters, excluding trailing spaces. DATALENGTH returns bytes, including trailing spaces -- and for nvarchar every character is 2 bytes (or more with supplementary characters):

SELECT LEN('abc   ');            -- 3
SELECT DATALENGTH('abc   ');     -- 6 (varchar, bytes)
SELECT DATALENGTH(N'abc');       -- 6 (nvarchar, 2 bytes/char)

If you need character count including trailing spaces, the standard idiom is LEN(col + 'x') - 1.

Finding: CHARINDEX and PATINDEX

CHARINDEX(substring, string [, start]) returns the 1-based position of a literal substring, 0 if not found. PATINDEX(pattern, string) does the same with LIKE wildcards:

SELECT CHARINDEX('@', 'ana@example.com');        -- 4
SELECT PATINDEX('%[0-9]%', 'Order 4711');        -- 7 (first digit)

Both return 0 (not NULL, not -1) when there's no match -- test with > 0. There is no SPLIT_PART equivalent; extracting "the part before the @" is LEFT(email, CHARINDEX('@', email) - 1), which throws on rows without an @ because LEFT with -1 is invalid. Guard with NULLIF:

SELECT LEFT(email, NULLIF(CHARINDEX('@', email), 0) - 1) FROM users;

Extracting and Modifying

• SUBSTRING(string, start, length) -- 1-based, length is required (unlike most other databases).
• LEFT(string, n) / RIGHT(string, n).
• REPLACE(string, old, new) -- all occurrences, case-insensitivity follows collation.
• TRANSLATE(string, from_chars, to_chars) (2017+) -- character-by-character replacement, one pass instead of nested REPLACE calls.
• STUFF(string, start, delete_count, insert) -- delete and insert at a position. The classic use is masking: STUFF(card_number, 1, 12, REPLICATE('*', 12)).
• REPLICATE, REVERSE, UPPER, LOWER, SPACE.

TRIM, LTRIM, RTRIM

TRIM arrived in SQL Server 2017; before that you nested LTRIM(RTRIM(col)). Since 2017 it can also strip a custom character set:

SELECT TRIM('#; ' FROM '### product;;');   -- 'product'

The LEADING / TRAILING / BOTH keywords (TRIM(TRAILING ';' FROM col)) require SQL Server 2022 and database compatibility level 160 -- on a restored older database they fail with a syntax error even on a 2022 instance.

STRING_SPLIT: One String to Rows

STRING_SPLIT (2016+, requires compatibility level 130) is a table-valued function -- use it with CROSS APPLY against a column:

SELECT o.order_id, TRIM(s.value) AS tag
FROM orders AS o
CROSS APPLY STRING_SPLIT(o.tags, ',') AS s;

Two long-standing caveats:

1. No ordinal guarantee before 2022. The output order is not guaranteed. SQL Server 2022 added a third argument: STRING_SPLIT(string, ',', 1) returns an ordinal column with each fragment's position.
2. Single-character separator only. Splitting on ', ' or a multi-char delimiter needs a REPLACE first or OPENJSON against a JSON array.

STRING_AGG: Rows to One String

STRING_AGG (2017+) is the aggregate inverse, with ordering via WITHIN GROUP:

SELECT region, STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) AS customers
FROM customers
GROUP BY region;

The trap: the return type follows the input type, so varchar/nvarchar inputs cap the result at 8000 bytes and a longer aggregation fails with "STRING_AGG aggregation result exceeded the limit of 8000 bytes". Cast the input, not the output:

STRING_AGG(CAST(name AS nvarchar(max)), ', ')

On 2016 and earlier the workaround is the STUFF(... FOR XML PATH('')) idiom -- if you're maintaining code with that pattern, it's a pre-2017 string aggregation, and STRING_AGG is the modern replacement.

Case Sensitivity Is Collation, Not Functions

There is no ILIKE and no case-insensitive flag: whether WHERE name = 'ana' matches 'Ana' depends on the column's collation (_CI_ = case-insensitive, _CS_ = case-sensitive). Most installs default to case-insensitive, which surprises people in the opposite direction from PostgreSQL. Forcing a comparison one way:

WHERE name COLLATE Latin1_General_CS_AS = 'Ana'

Wrapping the column in UPPER() works too but kills index use. If you compare varchar columns to N'...' literals, the column gets implicitly converted to nvarchar and the index is likewise skipped -- keep literal types matched to column types.

Regular Expressions: 2025 or Workarounds

Until SQL Server 2025 there was no regex support -- only LIKE/PATINDEX character classes (%[0-9]%), which do not support repetition, alternation, or anchors. SQL Server 2025 adds RE2-based functions: REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT, and the table-valued REGEXP_MATCHES / REGEXP_SPLIT_TO_TABLE:

-- SQL Server 2025
SELECT * FROM users WHERE REGEXP_LIKE(email, '^[^@]+@[^@]+\.[a-z]{2,}$', 'i');

Being RE2-based, backreferences and lookarounds are not supported. On 2022 and earlier, the honest answer is CLR functions or doing regex work in the application layer.

GREATEST and LEAST (2022)

Row-wise max/min across columns, NULL-ignoring -- previously an ugly CASE or CROSS APPLY (VALUES ...):

SELECT GREATEST(updated_at, imported_at, created_at) AS last_touched FROM leads;

Common Mistakes

SQL how-tos for other engines differ in exactly these corners -- see our MySQL, PostgreSQL, and SQLite string function guides for the same ground in each dialect. For working with delimited data at import time, see Import CSV to SQL Server.

Exploring string transformations is faster when autocomplete knows the dialect -- Mako's AI autocomplete suggests T-SQL functions (not the MySQL ones) when you're connected to SQL Server. Try it free at mako.ai.