Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 39 ยท Functions and Expressions

SQL Server String Functions

Master T-SQL string functions: LEN, LEFT, SUBSTRING, CHARINDEX, REPLACE, TRIM, CONCAT, STRING_SPLIT, FORMAT and pitfalls such as trailing spaces and NULL concatenation.

Real data is messy: names with extra spaces, codes glued together, emails that need splitting. T-SQL ships a solid set of string functions to clean, search, cut and combine text. This lesson goes through the ones you will use most, shows what they do with NULL and trailing spaces, and points out where the T-SQL name differs from other databases.

The portable functions run live; for the T-SQL-only ones, a labelled stand-in does the same job.

Length and trimming

Function Result
LEN(s) characters in s, ignoring trailing spaces
DATALENGTH(s) bytes used, including trailing spaces (2 per character for NVARCHAR)
LTRIM(s), RTRIM(s) remove leading / trailing spaces
TRIM(s) remove both ends (2017+); TRIM('x' FROM s) removes other characters
UPPER(s), LOWER(s) change case

LEN('abc ') returns 3, while DATALENGTH('abc ') returns 6. Surprising? It follows the ANSI rule that trailing spaces do not matter in comparisons, so 'abc' = 'abc ' is true in SQL Server.

SQL Length, trim and case ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Cutting text: LEFT, RIGHT, SUBSTRING

SQL
SELECT LEFT(N'SQL Server', 3),        -- 'SQL'
       RIGHT(N'SQL Server', 6),       -- 'Server'
       SUBSTRING(N'SQL Server', 5, 3);-- 'Ser'  (start at 5, take 3)

SUBSTRING(text, start, length) counts from 1, and the length argument is required in T-SQL (use LEN(text) to mean "to the end"). A start beyond the text returns an empty string, not an error.

Searching: CHARINDEX, PATINDEX, LIKE

CHARINDEX(find, text [, start]) returns the 1-based position of the first match, or 0 if none. PATINDEX('%pattern%', text) does the same with LIKE wildcards.

A classic task is splitting an email at the @:

SQL
SELECT email,
       LEFT(email, CHARINDEX(N'@', email) - 1)               AS user_name,
       SUBSTRING(email, CHARINDEX(N'@', email) + 1, 200)     AS domain
FROM dbo.customers;

If a row has no @, CHARINDEX returns 0 and LEFT(email, -1) raises an error. Guard it with NULLIF(CHARINDEX(N'@', email), 0) or a WHERE email LIKE N'%@%'. The editor uses SQLite's instr, the counterpart of CHARINDEX with the arguments reversed:

SQL Stand-in: split an email ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Replacing, padding, reversing

  • REPLACE(text, old, new) replaces every occurrence and is case-insensitive under a CI collation.
  • STUFF(text, start, length, new) deletes length characters from start and inserts new; with length 0 it is a pure insertion.
  • TRANSLATE(text, from_chars, to_chars) (2017+) maps characters one by one.
  • REVERSE(text), REPLICATE(text, n), SPACE(n) build and flip text.
  • FORMAT-like zero padding: RIGHT(REPLICATE('0', 5) + CAST(id AS VARCHAR(5)), 5) produces 00042.
SQL
SELECT REPLACE(N'2025-03-14', N'-', N'/')      AS slashes,      -- 2025/03/14
       STUFF(N'4155551234', 4, 0, N'-')        AS stuffed,      -- 415-5551234
       RIGHT(REPLICATE(N'0', 5) + N'42', 5)    AS padded;       -- 00042
SQL REPLACE and padding ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Combining text: +, CONCAT, CONCAT_WS

The + operator joins strings, but one NULL makes the whole result NULL. CONCAT (2012+) treats NULL as an empty string and converts numbers automatically. CONCAT_WS(separator, a, b, ...) (2017+) joins with a separator and skips NULLs:

SQL
SELECT N'Ada' + NULL                       AS plus_null,        -- NULL
       CONCAT(N'Ada', NULL, N'Lovelace')   AS concat_null,      -- AdaLovelace
       CONCAT_WS(N' ', N'Ada', NULL, N'Lovelace') AS with_sep;  -- Ada Lovelace

In the editor, || is the standard concatenation operator and behaves like + with respect to NULL:

SQL NULL and concatenation ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Splitting and joining sets: STRING_SPLIT and STRING_AGG

STRING_SPLIT(text, separator) (2016+) is a table-valued function: it returns a row per piece, in a column named value.

SQL
SELECT value AS tag
FROM STRING_SPLIT(N'sql,tsql,indexing', N',');

From SQL Server 2022 you can add a third argument, 1, to also return an ordinal column that keeps the position (without it, the order is not guaranteed). The reverse operation, STRING_AGG, joins rows into one string, as shown in the aggregation lesson. A recursive CTE gives a portable splitter, used as the stand-in below:

SQL Stand-in: STRING_SPLIT with a recursive CTE

Press Run (or Ctrl + Enter) to see the result here.

FORMAT and number-to-text

FORMAT(value, 'pattern' [, 'culture']) applies .NET format strings: FORMAT(1234.5, 'N2', 'en-US') gives 1,234.50, and FORMAT(SYSDATETIME(), 'yyyy-MM-dd') gives a date text. It is convenient but slow (it calls the .NET runtime per row), so avoid it over many rows; use CONVERT with a style number instead (see conversion functions). STR(number, length, decimals) is an older number formatter.

Unicode helpers and collation

UNICODE(c) returns a character's code point and NCHAR(n) builds a character from it; ASCII and CHAR do the same for single-byte values. SOUNDEX and DIFFERENCE compare how words sound, which helps with fuzzy name matching. Remember that comparisons depend on collation: UPPER is rarely needed for matching under a case-insensitive collation.

Wrapping an indexed column in a string function, such as WHERE LEFT(name, 3) = N'Sql', prevents an index seek. Rewrite it as WHERE name LIKE N'Sql%'.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does LEN('abc ') return?

    LEN ignores trailing spaces. DATALENGTH would count them.
  2. 2. What does N'Ada' + NULL return?

    The + operator returns NULL if either operand is NULL. CONCAT treats NULL as an empty string instead.
  3. 3. Which function returns the position of a substring in T-SQL?

    CHARINDEX(find, text) returns a 1-based position, or 0 when not found. INSTR is Oracle, MySQL and SQLite.
  4. 4. What does STRING_SPLIT('a,b,c', ',') return?

    STRING_SPLIT is a table-valued function returning one row per piece.
  5. 5. Why is FORMAT discouraged for large result sets?

    FORMAT is convenient but much slower than CONVERT with a style or simple string functions.

Summary

  • LEN ignores trailing spaces; DATALENGTH counts bytes.
  • SUBSTRING needs a start and length; CHARINDEX finds positions (0 means not found).
  • + propagates NULL; CONCAT and CONCAT_WS handle it gracefully.
  • STRING_SPLIT returns rows, STRING_AGG joins rows; REPLACE, STUFF and TRANSLATE edit text.
  • Avoid string functions on indexed columns inside WHERE, and avoid FORMAT on big sets.

Next: Date and Time Functions