T-SQL String Functions: LEN vs DATALENGTH, CHARINDEX, SUBSTRING, REPLACE, STUFF, TRANSLATE, CONCAT_WS, STRING_AGG and STRING_SPLIT

Version note: The SQL queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10) with the series' Bookstore database; the PostgreSQL comparisons were run on PostgreSQL 18.7.

Guess First: Does REPLACE Change an Uppercase Letter?

We want to replace the lowercase letter "d" in a book title with "X". "Dune Messiah" has no lowercase "d"; its only "D" is uppercase, at the start of the word. Guess the result of the three columns:

SELECT REPLACE(N'Dune Messiah', N'd', N'X') AS replaced,
       CHARINDEX(N'MESSIAH', N'Dune Messiah') AS position_ci,
       REPLACE(N'Dune Messiah' COLLATE Latin1_General_100_CS_AS, N'd', N'X') AS replaced_cs;

replaced      position_ci  replaced_cs
------------  -----------  ------------
Xune Messiah  6            Dune Messiah

(1 rows affected)

With the habits of C#'s string.Replace you would say "nothing changes", but SQL Server replaced the uppercase "D" as well. CHARINDEX also found MESSIAH written in capitals. The reason is collation: the database of Bookstore, the online bookstore used as the shared example across this site's series, uses SQL_Latin1_General_CP1_CI_AS, and CI means case-insensitive. T-SQL's string functions use the input's collation when they compare; in the third column, after switching to a case-sensitive (CS) collation, "d" matched nowhere. In post 03 of the series we saw how collation affects sorting and comparison; the same rule applies to REPLACE, CHARINDEX and TRANSLATE. In PostgreSQL, replace('Dune Messiah', 'd', 'X') returns "Dune Messiah", because string functions there are always case-sensitive.

In post 07 we gave a general introduction to scalar functions and covered CONCAT, LEN/DATALENGTH and sargability. This post and the next three go through T-SQL's everyday functions one by one: what they do, how they handle NULL, their edge cases and their PostgreSQL counterparts. This post is about string functions.

Finding Positions and Taking Parts: CHARINDEX, PATINDEX, LEFT, RIGHT and SUBSTRING

CHARINDEX(search, text [, start]) returns the first position of the search expression, counting from 1. If it is not found it returns 0; if any argument is NULL it returns NULL. The third argument says which character the search starts from. PATINDEX accepts the wildcards of LIKE, but you have to put % at the start and the end of the pattern:

SELECT CHARINDEX(N'e', N'Middlemarch: A Study of Provincial Life') AS first_e,
       CHARINDEX(N'e', N'Middlemarch: A Study of Provincial Life', 7) AS next_e,
       CHARINDEX(N'x', N'Middlemarch') AS not_found,
       CHARINDEX(NULL, N'Middlemarch') AS null_search,
       PATINDEX(N'%[0-9]%', N'Fahrenheit 451') AS first_digit,
       SUBSTRING(N'Fahrenheit 451', PATINDEX(N'%[0-9]%', N'Fahrenheit 451'), 10) AS digits;

first_e  next_e  not_found  null_search  first_digit  digits
-------  ------  ---------  -----------  -----------  ------
6        39      0          NULL         12           451

The fact that "not found" is 0 matters: in an expression like SUBSTRING(x, CHARINDEX(...) + 1, ...), a missing separator produces a part that starts at position 1, without an error. When you write such an expression, you have to answer "what if the separator is missing" separately.

LEFT, RIGHT and SUBSTRING are tolerant of out-of-range values. Ask for a length longer than the string and you get what there is. Give SUBSTRING a start of 0 or a negative start and the "imaginary" positions before the string count too, so the result gets shorter:

SELECT LEFT(N'Dune', 10) AS left_too_long, SUBSTRING(N'Dune', 3, 100) AS sub_too_long,
       SUBSTRING(N'Dune', 0, 3) AS sub_from_zero, SUBSTRING(N'Dune', -1, 3) AS sub_negative;

left_too_long  sub_too_long  sub_from_zero  sub_negative
-------------  ------------  -------------  ------------
Dune           ne            Du             D

SUBSTRING(N'Dune', 0, 3) covers positions 0, 1 and 2; there is no character at position 0, so only two characters came back. The tolerance ends at a negative length:

SELECT LEFT(N'Dune', -1) AS left_negative;

Msg 536, Level 16, State 6, Line 1
Invalid length parameter passed to the left function.

This error usually shows up in an expression like LEFT(x, CHARINDEX(' ', x) - 1) when the query reaches the first row without the separator: CHARINDEX returns 0, the length becomes -1 and the query stops at that row.

A real example: a supplier sends its author list as "First Last" in a single column, and we want to show "Last, First" in the catalog. The last name is the part after the last space; T-SQL has no "last occurrence" function, so we reverse the string with REVERSE and search for the first space:

DROP TABLE IF EXISTS dbo.supplier_authors;
CREATE TABLE dbo.supplier_authors (
    line_no   INT           NOT NULL PRIMARY KEY,
    full_name NVARCHAR(100) NOT NULL
);
INSERT INTO dbo.supplier_authors (line_no, full_name)
SELECT author_id, CONCAT_WS(N' ', first_name, last_name)
FROM dbo.authors
WHERE author_id IN (1, 10, 20, 28, 67);

SELECT full_name,
       CONCAT(RIGHT(full_name, CHARINDEX(N' ', REVERSE(full_name)) - 1), N', ',
              LEFT(full_name, LEN(full_name) - CHARINDEX(N' ', REVERSE(full_name)))) AS sort_name
FROM dbo.supplier_authors
ORDER BY line_no;

full_name             sort_name
--------------------  ---------------------
Jane Austen           Austen, Jane
Miguel de Cervantes   Cervantes, Miguel de
Ursula K. Le Guin     Guin, Ursula K. Le
J. R. R. Tolkien      Tolkien, J. R. R.
Ahmet Hamdi Tanpınar  Tanpınar, Ahmet Hamdi

Three of the five names are right. "Cervantes, Miguel de" and "Guin, Ursula K. Le" are wrong, because "de" and "Le" belong to the last name. You cannot turn this into a rule with string functions; keeping first and last names in separate columns from the start is the only reliable solution. That is why Bookstore's authors table keeps first_name and last_name apart. When importing supplier data, this kind of splitting only produces a suggestion; a person has to check the result.

Changing Text: REPLACE, STUFF, TRANSLATE, REPLICATE and REVERSE

REPLACE replaces every occurrence of an expression. STUFF(text, start, length_to_delete, insert) works by position: it deletes a part starting at the given position and puts something else in its place. With a delete length of 0 it only inserts. To mask an e-mail address, let us replace everything after the first character up to the @ with asterisks:

SELECT TOP (3) email,
       STUFF(email, 2, CHARINDEX('@', email) - 2, REPLICATE('*', CHARINDEX('@', email) - 2)) AS masked
FROM dbo.customers
ORDER BY customer_id;

email                           masked
------------------------------  ------------------------------
ada.lovelace@example.com        a***********@example.com
Alan.Turing@Example.com         A**********@Example.com
grace.hopper+books@example.org  g*****************@example.org

REPLICATE(text, n) repeats the text n times; with n = 0 it returns an empty string, with a negative n it returns NULL. The same function helps to make an ISBN readable on screen: STUFF(isbn, 4, 0, '-') turns "9798900000268" into "979-8900000268". Real ISBN hyphenation depends on country and publisher ranges, so you cannot produce correct hyphens with string functions alone without a lookup table.

TRANSLATE(text, characters, replacements) arrived in SQL Server 2017 and works character by character: it replaces each character of the second argument with the character at the same position in the third. To build an ASCII slug from Turkish book titles, one call is enough instead of six nested REPLACE calls:

SELECT title,
       LOWER(REPLACE(TRANSLATE(title, N'çğıöşüÇĞİÖŞÜ', N'cgiosuCGIOSU'), N' ', N'-')) AS slug
FROM dbo.books
WHERE book_id BETWEEN 74 AND 77
ORDER BY book_id;

title                        slug
---------------------------  ---------------------------
Kürk Mantolu Madonna         kurk-mantolu-madonna
İnce Memed                   ince-memed
Tutunamayanlar               tutunamayanlar
Saatleri Ayarlama Enstitüsü  saatleri-ayarlama-enstitusu

The two lists must have the same length; PostgreSQL's translate deletes the extra characters, while T-SQL raises an error:

SELECT TRANSLATE(N'abc', N'ab', N'xyz') AS bad_translate;

Msg 9828, Level 16, State 3, Line 1
The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

TRANSLATE can map a character only to a single character; for jobs like turning "ß" into "ss" you still need REPLACE. Like REPLACE, it also follows the collation: in this database TRANSLATE(N'Dune', N'd', N'X') returns "Xune" as well. REVERSE is mostly used, as above, in the "find the last occurrence" trick.

Case, Whitespace and the N Prefix

UPPER and LOWER also convert according to the collation. That is why the Turkish "i" and "ı" need special care:

SELECT UPPER(N'istanbul') AS upper_db,
       UPPER(N'istanbul' COLLATE Turkish_100_CI_AS) AS upper_turkish,
       LOWER(N'IŞIK') AS lower_db,
       LOWER(N'IŞIK' COLLATE Turkish_100_CI_AS) AS lower_turkish;

upper_db  upper_turkish  lower_db  lower_turkish
--------  -------------  --------  -------------
ISTANBUL  İSTANBUL       işik      ışık

The database's default collation turned "IŞIK" into "işik": it lowered "Ş" correctly, but turned "I" into "i" instead of the Turkish "ı". If you change the case of Turkish text, write the collation explicitly in the expression. We covered this in detail with the Turkish I trap in post 03.

A sneakier problem is forgetting the N prefix on a string literal. N'...' is an NVARCHAR (Unicode) literal; '...' without the prefix is a VARCHAR literal in the database's code page. This database's code page is 1252, which has no "İ", "ı" or "ş":

DECLARE @t TABLE (id INT, title_v VARCHAR(50), title_n NVARCHAR(50));
INSERT INTO @t VALUES
    (1, 'İnce Memed', 'İnce Memed'),
    (2, N'İnce Memed', N'İnce Memed'),
    (3, 'Tutunamayanlar ışık', N'Tutunamayanlar ışık');
SELECT id, title_v, title_n FROM @t;

id  title_v              title_n
--  -------------------  -------------------
1   Ince Memed           Ince Memed
2   Ince Memed           İnce Memed
3   Tutunamayanlar isik  Tutunamayanlar ışık

In the first row the data was damaged even though the column is NVARCHAR: the literal had no prefix, so "İ" became "I" before it ever reached the column. No error, no warning. In the second and third rows the VARCHAR column could not store these characters either. The rule is simple: every literal that goes into an NVARCHAR column is written with the N prefix. In post 07 we saw the reverse case, a VARCHAR column searched with an NVARCHAR parameter losing its index; think of both rules together: the literal's type should match the column's type.

LTRIM, RTRIM and TRIM remove only the space character (32) by default; tabs and line breaks stay. You can give TRIM the characters to remove with FROM (since 2017; the second argument of LTRIM/RTRIM arrived with 2022, as we saw in post 17):

SELECT LEN(TRIM(CHAR(9) + N'Emma' + CHAR(13) + CHAR(10))) AS default_trim,
       LEN(TRIM(CHAR(9) + CHAR(13) + CHAR(10) + N' ' FROM CHAR(9) + N'Emma' + CHAR(13) + CHAR(10))) AS whitespace_trim;

default_trim  whitespace_trim
------------  ---------------
7             4

A trailing line break is common in values copied from Excel or a form, and it silently breaks a match like WHERE title = N'Emma'.

Finally QUOTENAME: it wraps an object name in square brackets and doubles any ] inside it. This is the safe way to add a table or column name to a string in dynamic SQL:

SELECT QUOTENAME(N'order items') AS bracketed,
       QUOTENAME(N'odd]name') AS escaped,
       QUOTENAME(N'O''Neil', '''') AS single_quoted,
       QUOTENAME(REPLICATE(N'x', 129)) AS too_long;

bracketed      escaped      single_quoted  too_long
-------------  -----------  -------------  --------
[order items]  [odd]]name]  'O''Neil'      NULL

If the input is longer than 128 characters, QUOTENAME returns NULL, because an identifier in SQL Server can be at most 128 characters. Dynamic SQL and parameters are covered in the SQL Server Derinlemesine series post "Stored Procedures: Parameters, Error Handling, Ownership Chaining and Safe Dynamic SQL".

Concatenating: +, CONCAT, CONCAT_WS and STRING_AGG

The three ways of concatenating handle NULL differently. Barbara Liskov's city is NULL:

SELECT customer_id, first_name, city,
       first_name + N' (' + city + N')' AS plus_op,
       CONCAT(first_name, N' (', city, N')') AS concat_fn,
       CONCAT_WS(N' / ', first_name, city, country_code) AS concat_ws_fn
FROM dbo.customers
WHERE customer_id IN (6, 7)
ORDER BY customer_id;

customer_id  first_name  city    plus_op          concat_fn        concat_ws_fn
-----------  ----------  ------  ---------------  ---------------  --------------------
6            Donald      Berlin  Donald (Berlin)  Donald (Berlin)  Donald / Berlin / DE
7            Barbara     NULL    NULL             Barbara ()       Barbara / DE

+ turns the whole result into NULL on a single NULL (so do the standard || and SQL Server 2025's ||; see post 18). CONCAT treats NULL as an empty string and produces half-finished text like "Barbara ()". CONCAT_WS skips a NULL argument together with its separator. An empty string, however, is not NULL: CONCAT_WS(N', ', N'Emma', N'', N'Dune') returns "Emma, , Dune". And when all arguments are NULL, CONCAT and CONCAT_WS return an empty string, not NULL.

That last detail sets a trap when combined with STRING_AGG. In post 10 we saw STRING_AGG and ordering with WITHIN GROUP. STRING_AGG skips NULL values; but if you prepare its input with CONCAT_WS, NULL turns into an empty string and is not skipped. Beowulf has no author rows:

SELECT b.book_id,
       STRING_AGG(CONCAT_WS(N' ', a.first_name, a.last_name), N'; ') AS via_concat_ws,
       STRING_AGG(a.last_name, N'; ') AS via_column
FROM dbo.books AS b
LEFT JOIN dbo.book_authors AS ba ON ba.book_id = b.book_id
LEFT JOIN dbo.authors AS a ON a.author_id = ba.author_id
WHERE b.book_id IN (24, 42)
GROUP BY b.book_id
ORDER BY b.book_id;

book_id  via_concat_ws                 via_column
-------  ----------------------------  -----------------
24                                     NULL
42       Terry Pratchett; Neil Gaiman  Pratchett; Gaiman

For Beowulf the first column is an empty string, the second is NULL. The API serializes these two cases differently ("" and null), and an "author unknown" check on the Angular side works for one and not the other. Decide which one you want and, if needed, turn the empty string back into NULL with NULLIF(..., N'').

The second trap of STRING_AGG is its return type. If the input is VARCHAR(1..8000), the result is VARCHAR(8000); for NVARCHAR(1..4000) it is NVARCHAR(4000). If the result exceeds that limit, it is not truncated; the query fails:

SELECT LEN(STRING_AGG(CAST(CONCAT(o.order_id, ':', c.email) AS VARCHAR(300)), ',')) AS too_long
FROM dbo.orders AS o
JOIN dbo.customers AS c ON c.customer_id = o.customer_id;

Msg 9829, Level 16, State 1, Line 1
STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

With the input cast to VARCHAR(MAX), the same query returned an 11,295-character result. A report that works with 40 customers in test data stops with this error once the data grows; if you do not know how long the list can get, cast the input to a MAX type from the start.

Splitting: STRING_SPLIT

The other direction: turning a comma-separated id list into rows. When a multi-select filter in Angular sends a parameter like ?ids=26,42,60, this is the first approach that comes to mind. STRING_SPLIT has existed since 2016, but three of its details are always forgotten:

SELECT '[' + value + ']' AS value, ordinal
FROM STRING_SPLIT('12,7,,42, 26', ',', 1)
ORDER BY ordinal;

value  ordinal
-----  -------
[12]   1
[7]    2
[]     3
[42]   4
[ 26]  5

First, an empty gap between two separators produces an empty-string row. Second, values are not trimmed: " 26" came with its leading space. Third, order. According to Microsoft Learn, rows returned without ordinal do not have to follow the order of the input. The enable_ordinal argument (the third argument, 1) arrived with SQL Server 2022 and returns each part's position in the input as a bigint; if order matters, use it and write ORDER BY ordinal.

The value column is a string. If you compare it directly with an INT column, SQL Server tries to convert every value to INT and the query stops at the first bad value:

DECLARE @ids VARCHAR(100) = '26,42,abc';
SELECT b.book_id, b.title
FROM dbo.books AS b
JOIN STRING_SPLIT(@ids, ',') AS s ON s.value = b.book_id;

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'abc' to data type int.

Convert with TRY_CAST and use a LEFT JOIN, and you see both the bad value and any id that was not found:

DECLARE @ids VARCHAR(100) = '26,42,abc,60, 39';
SELECT s.ordinal, s.value, b.book_id, b.title
FROM STRING_SPLIT(@ids, ',', 1) AS s
LEFT JOIN dbo.books AS b ON b.book_id = TRY_CAST(s.value AS INT)
ORDER BY s.ordinal;

ordinal  value  book_id  title
-------  -----  -------  ---------------
1        26     26       Dune
2        42     42       Good Omens
3        abc    NULL     NULL
4        60     60       Design Patterns
5         39    39       The Hobbit

TRY_CAST did not mind the leading space and turned " 39" into 39. The separator must be a single character; STRING_SPLIT('Dune -- Emma', '--') is rejected with "Procedure expects parameter 'separator' of type 'nchar(1)/nvarchar(1)'" (Msg 214). For multi-character or pattern separators there is REGEXP_SPLIT_TO_TABLE from post 18. If the application sends many ids, a table-valued parameter or a JSON parameter is more robust than a comma-separated string; you can find the table-valued parameter in the Dapper series post on SQL Server-specific parameters, and the JSON parameter in this series' upcoming JSON post.

Function Summary and PostgreSQL Counterparts

The NULL behaviour of this post's functions and their counterparts in PostgreSQL 18. I ran every expression in the PostgreSQL column in my own container:

Two differences in the table come up often in daily work. In PostgreSQL length('Dune ') returns 7, while T-SQL's LEN returns 4. Taking the last word is a single step in PostgreSQL: split_part('Ursula K. Le Guin', ' ', -1) returns "Guin" (negative positions arrived in PostgreSQL 14); in T-SQL the same job needs the REVERSE trick above.

In Production

The real cost of string functions shows up, as we saw in post 07, when they are wrapped around a column in WHERE. LEFT and LIKE ask the same question, but the plans differ:

SET SHOWPLAN_TEXT ON;
GO
SELECT customer_id FROM dbo.customers WHERE LEFT(email, 5) = 'grace';
SELECT customer_id FROM dbo.customers WHERE email LIKE 'grace%';
GO
SET SHOWPLAN_TEXT OFF;

  |--Index Scan(OBJECT:([sqlseries_ext_19].[dbo].[customers].[UQ_customers_email]),  WHERE:(substring([sqlseries_ext_19].[dbo].[customers].[email],(1),(5))='grace'))

  |--Index Seek(OBJECT:([sqlseries_ext_19].[dbo].[customers].[UQ_customers_email]), SEEK:([sqlseries_ext_19].[dbo].[customers].[email] >= 'gracDþ' AND [sqlseries_ext_19].[dbo].[customers].[email] < 'gracF'),  WHERE:([sqlseries_ext_19].[dbo].[customers].[email] like 'grace%') ORDERED FORWARD)

The optimizer turned LEFT into substring and scanned the whole index; LIKE 'grace%' became a range seek. If you compute the same string expression in every query (say, the e-mail domain), putting it in a computed column and indexing it is a separate design decision; the SQL Server Derinlemesine series covers it together with indexes.

The second topic is type. When VARCHAR and NVARCHAR mix, you get both data loss (unprefixed literals) and index loss (implicit conversion). The result type of functions like REPLACE, STUFF and CONCAT comes from the input type; one NVARCHAR input makes the whole expression NVARCHAR. Third, large text: on non-MAX types both functions are bound to the 8000-byte limit, but they behave differently. STRING_AGG fails as shown above; REPLACE silently cuts the result. Replacing every "a" with "bb" in an 8000-character VARCHAR gave 8,000 characters instead of 16,000; with a VARCHAR(MAX) input it gave 16,000. Test a report with the longest value in production, not with small test data.

Common Pitfalls

• Forgetting that REPLACE and CHARINDEX follow the collation; in a case-insensitive database a search for "d" also finds "D".
• Forgetting the N prefix on a literal that goes into an NVARCHAR column; "İ", "ı" and "ş" get damaged without any warning.
• Writing LEFT(x, CHARINDEX(...) - 1) without thinking about the row where CHARINDEX returns 0 (Msg 536).
• Relying on the order of STRING_SPLIT output; if order matters, use enable_ordinal and ORDER BY ordinal.
• Comparing STRING_SPLIT's value directly with an INT column; the query stops at the first bad value.
• Running STRING_AGG on VARCHAR(n) input over large groups (Msg 9829), and CONCAT_WS producing an empty string instead of NULL.
• Making an index unusable with WHERE LEFT(column, n) = ...; write LIKE 'prefix%'.

A Small Experiment

★☆☆ Extract the domain from the e-mail addresses in the customers table with SUBSTRING and CHARINDEX, normalize it with LOWER and count the customers per domain. Then add a test address without @ and see what the query returns.

★★☆ For each category, collect the book titles in alphabetical order into a single column (STRING_AGG ... WITHIN GROUP). Does the Poetry category, which has no books, show NULL or an empty string? Decide which of the two you want and fix the query accordingly.

★★★ Turn the "First Last" splitting example into a query that keeps a list of last-name prefixes (de, Le, van, von) in a table and joins the last two words when the second-to-last word is in that list. Do Miguel de Cervantes and Ursula K. Le Guin come out right? Which name is still wrong?

In the next post we move on to date and time functions: the difference between SYSDATETIME and SYSUTCDATETIME, DATEADD, the boundary-counting logic of DATEDIFF, EOMONTH, DATEFROMPARTS and converting between UTC and Istanbul time with AT TIME ZONE.