String Functions in SQL Server: Part One

This part covers nine of SQL Server's built-in string functions: ASCII, CHAR, CHARINDEX, DIFFERENCE, LEFT, LEN, LOWER, LTRIM and NCHAR. Here they are at a glance:

Function	Returns	Example	Result
ASCII(string)	Code of the first character	ASCII('A')	65
CHAR(int)	Character for a code from 0 to 255	CHAR(65)	A
NCHAR(int)	Unicode character for a code point	NCHAR(8364)	€
CHARINDEX(find, string [, start])	Position of the first match, or 0	CHARINDEX('SQL', 'Microsoft SQL Server')	11
LEFT(string, n)	The first n characters	LEFT('SQL Server', 3)	SQL
LEN(string)	Number of characters, ignoring trailing spaces	LEN('SQL Server')	10
LOWER(string)	The string in lowercase	LOWER('SQL Server')	sql server
LTRIM(string)	The string without leading spaces	LTRIM(' SQL')	SQL
DIFFERENCE(a, b)	Sound similarity from 0 to 4	DIFFERENCE('Smith', 'Smyth')	4

Two rules apply to all of them. Positions are counted from 1, not 0. And if the string argument is NULL, the result is NULL.

Sample table

CREATE TABLE dbo.Customers (
    CustomerId int          NOT NULL PRIMARY KEY,
    FullName   varchar(50)  NOT NULL,
    Email      varchar(100) NULL
);

INSERT INTO dbo.Customers VALUES
(1, '  Ajay Kumar',   'Ajay.Kumar@Example.com'),
(2, 'Deepak Singh  ', 'deepak@example.org'),
(3, 'Kapil Mehta',    'kapil.mehta'),
(4, 'Zoe Martin',     NULL);

Row 1 has two leading spaces, row 2 has two trailing spaces, row 3 has an email with no @, and row 4 has no email. Real data looks like this, and each of those details breaks a naive query somewhere below.

ASCII, CHAR and NCHAR: characters and codes

ASCII returns the code of the first character of a string and ignores the rest. CHAR goes the other way.

SELECT ASCII('A')      AS a,   -- 65
       ASCII('apple')  AS b,   -- 97, the code for 'a'
       CHAR(65)        AS c,   -- A
       CHAR(300)       AS d;   -- NULL, outside 0 to 255

The most common use of CHAR is to put control characters into a string:

SELECT 'Line 1' + CHAR(13) + CHAR(10) + 'Line 2';   -- carriage return + line feed

CHAR(9) is a tab, CHAR(10) a line feed and CHAR(13) a carriage return. SSMS shows line breaks in Results to Text mode. The grid shows them as spaces.

ASCII is also the tool for finding out what an invisible character really is. If a value looks as though it starts with a space but LTRIM does not remove it, check the code:

SELECT FullName, ASCII(FullName) AS FirstCharCode
FROM dbo.Customers;

Row 1 returns 32, an ordinary space. A result of 9 would be a tab and 160 a non-breaking space, neither of which LTRIM removes by default.

Codes 0 to 127 mean the same thing everywhere. Codes 128 to 255 depend on the code page of the database's default collation, so CHAR(128) is € on a Latin1 (code page 1252) database and something else on others. For anything outside plain ASCII, use the Unicode pair instead: NCHAR and UNICODE.

SELECT NCHAR(8364)    AS euro,       -- €
       NCHAR(937)     AS omega,      -- Ω
       UNICODE(N'€')  AS code_point; -- 8364

NCHAR accepts 0 to 65535. Code points above that, which include emoji, work only when the database uses a supplementary-character collation (one with _SC in its name). Otherwise NCHAR returns NULL.

CHARINDEX: find one string inside another

CHARINDEX(expressionToFind, expressionToSearch [, start_location])

The thing you are looking for comes first. Getting the arguments the wrong way round is not an error. It returns 0.

SELECT CHARINDEX('SQL', 'Microsoft SQL Server') AS a,   -- 11
       CHARINDEX('e', 'SQL Server')             AS b,   -- 6
       CHARINDEX('e', 'SQL Server', 7)          AS c,   -- 9, search starts at position 7
       CHARINDEX('x', 'SQL Server')             AS d;   -- 0, not found

Points to remember:

• "Not found" is 0, not NULL and not -1.
• No wildcards. For patterns, use PATINDEX or LIKE.
• Whether the search is case-sensitive depends on the collation. Most databases are case-insensitive. To force a case-sensitive search, add a collation: CHARINDEX('sql', 'Microsoft SQL Server' COLLATE Latin1_General_100_CS_AS) returns 0.
• WHERE CHARINDEX('kumar', FullName) > 0 cannot use an index seek, just like LIKE '%kumar%'. It scans every row.

LEFT: take characters from the start

SELECT LEFT('SQL Server', 3) AS a,   -- SQL
       LEFT('SQL', 10)       AS b,   -- SQL, no error and no padding when the string is shorter
       LEFT('SQL', 0)        AS c;   -- empty string

A negative length is an error, and that is exactly what happens when you combine LEFT with CHARINDEX to split a string and the delimiter is missing:

SELECT LEFT(Email, CHARINDEX('@', Email) - 1) AS UserName
FROM dbo.Customers;

Msg 537: Invalid length parameter passed to the LEFT or SUBSTRING function.

Row 3 has no @, so CHARINDEX returns 0 and the length becomes -1. One bad row fails the whole query. The usual fix is to append the delimiter to the string being searched, so it is always found:

SELECT CustomerId,
       LEFT(Email, CHARINDEX('@', Email + '@') - 1) AS UserName
FROM dbo.Customers;

CustomerId  UserName
----------  -----------
1           Ajay.Kumar
2           deepak
3           kapil.mehta
4           NULL

LEN: count characters

LEN returns the number of characters, not counting trailing spaces. Leading spaces are counted.

SELECT LEN('SQL Server')    AS a,   -- 10
       LEN('SQL   ')        AS b,   -- 3
       LEN('   SQL')        AS c,   -- 6
       LEN('')              AS d,   -- 0
       DATALENGTH('SQL   ') AS e,   -- 6
       DATALENGTH(N'SQL')   AS f;   -- 6

DATALENGTH returns bytes, not characters. It counts trailing spaces, and it returns two bytes per character for nchar and nvarchar data. To count characters including trailing spaces, a common trick is LEN(col + 'x') - 1.

Two more details. A character above code point 65535, such as an emoji, counts as 2 unless the collation is a supplementary-character (_SC) one. And LEN returns bigint for varchar(max) and nvarchar(max) input, int otherwise.

LOWER: convert to lowercase

SELECT LOWER('SQL Server');   -- sql server

LOWER and its counterpart UPPER are for display and for normalizing stored values, such as email addresses. They are usually unnecessary in comparisons, because under a case-insensitive collation 'ABC' = 'abc' is already true. Worse, wrapping a column in a function stops SQL Server from seeking on an index:

-- scans: the function hides the column from the index
SELECT CustomerId FROM dbo.Customers WHERE LOWER(Email) = 'deepak@example.org';

-- seeks, and matches the same rows under a case-insensitive collation
SELECT CustomerId FROM dbo.Customers WHERE Email = 'deepak@example.org';

If the column really is case-sensitive and you need case-insensitive lookups, store a normalized copy in a persisted computed column and index that.

LTRIM: remove leading spaces

SELECT LTRIM('   SQL') AS a;   -- SQL

By default LTRIM removes the space character only. Tabs, line breaks and non-breaking spaces stay where they are.

What is available depends on the version:

Need	Function	Available from
Remove leading spaces	LTRIM(string)	All versions
Remove trailing spaces	RTRIM(string)	All versions
Remove both	TRIM(string)	SQL Server 2017
Remove other leading characters	LTRIM(string, characters)	SQL Server 2022, with database compatibility level 160

SELECT LTRIM('000123', '0');   -- 123, on SQL Server 2022 or later

On older versions, LTRIM(RTRIM(col)) is the way to trim both ends.

DIFFERENCE: compare how two strings sound

DIFFERENCE computes the SOUNDEX code of each string, a letter followed by three digits that roughly represents how an English word sounds, and returns how many of the four positions match: 4 is the closest match and 0 the weakest.

SELECT SOUNDEX('Smith')                      AS a,   -- S530
       SOUNDEX('Smyth')                      AS b,   -- S530
       DIFFERENCE('Smith', 'Smyth')          AS c,   -- 4
       DIFFERENCE('Green', 'Greene')         AS d,   -- 4
       DIFFERENCE('Blotchet-Halls', 'Greene') AS e;  -- 0

It can be useful for finding likely duplicates among surnames, for example WHERE DIFFERENCE(a.LastName, b.LastName) = 4. Treat it as a rough filter only. SOUNDEX was designed for English surnames, looks mainly at the first few consonants, gives many false matches, and is of little use for other languages or for long strings. Like any function on a column, it also prevents index seeks.

SQL Server 2025 introduces edit-distance and Jaro-Winkler functions for fuzzy matching, such as EDIT_DISTANCE. Microsoft's edition documentation lists them as requiring the PREVIEW_FEATURES database-scoped configuration, so check their status in your build before depending on them.

Putting them together

SELECT CustomerId,
       LTRIM(FullName)                                     AS Name,
       LEN(FullName)                                       AS NameLength,
       LOWER(LEFT(Email, CHARINDEX('@', Email + '@') - 1)) AS UserName
FROM dbo.Customers;

CustomerId  Name            NameLength  UserName
----------  --------------  ----------  -----------
1           Ajay Kumar      12          ajay.kumar
2           Deepak Singh    12          deepak
3           Kapil Mehta     11          kapil.mehta
4           Zoe Martin      10          NULL

Row 1 has a length of 12 because its two leading spaces count. Row 2 is also 12, because its two trailing spaces do not. LTRIM left those trailing spaces on row 2's name in place.

Common mistakes

• Assuming positions start at 0. The first character is position 1, and CHARINDEX returns 0 for "not found".
• Using LEN to detect trailing spaces. It ignores them. Compare DATALENGTH values instead.
• Forgetting NULL. Every function here returns NULL for NULL input, and 'text' + NULL is NULL as well. Use CONCAT, which treats NULL as an empty string, or ISNULL.
• Mixing varchar and nvarchar. A string literal without the N prefix is varchar. Characters outside the database's code page turn into ? before the function ever sees them. Write N'...' for Unicode text.
• Cleaning data in every query. If you trim or lowercase a column each time you read it, fix the stored values once and add a constraint.

Further reading

• String functions (Transact-SQL)
• CHARINDEX (Transact-SQL)
• LTRIM (Transact-SQL)
• Collation and Unicode support