SQL Server String Functions

Quick reference for T-SQL string functions in SQL Server. All functions work in SELECT, WHERE, and computed columns unless noted.

Length & Trimming

Function	Description	Example	Result
`LEN(str)`	Length excluding trailing spaces	`LEN('abc ')`	`3`
`DATALENGTH(str)`	Byte length including trailing spaces	`DATALENGTH('abc ')`	`6`
`LTRIM(str)`	Remove leading spaces	`LTRIM(' abc')`	`'abc'`
`RTRIM(str)`	Remove trailing spaces	`RTRIM('abc ')`	`'abc'`
`TRIM(str)`	Remove leading and trailing spaces (2017+)	`TRIM(' abc ')`	`'abc'`
`TRIM(chars FROM str)`	Remove specific characters (2017+)	`TRIM('.' FROM '..abc..')`	`'abc'`

LEN excludes trailing spaces; DATALENGTH includes them and counts bytes (double for NVARCHAR).

Case Conversion

Function	Description	Example	Result
`UPPER(str)`	Convert to uppercase	`UPPER('Hello')`	`'HELLO'`
`LOWER(str)`	Convert to lowercase	`LOWER('Hello')`	`'hello'`

SQL Server has no built-in title-case function. Use a CLR function or a multi-step expression for that.

Substrings & Position

Function	Description	Example	Result
`SUBSTRING(str, start, len)`	Extract substring (1-indexed)	`SUBSTRING('abcdef', 2, 3)`	`'bcd'`
`LEFT(str, n)`	First n characters	`LEFT('abcdef', 3)`	`'abc'`
`RIGHT(str, n)`	Last n characters	`RIGHT('abcdef', 3)`	`'def'`
`CHARINDEX(substr, str)`	Position of first occurrence (0 if not found)	`CHARINDEX('bc', 'abcabc')`	`2`
`CHARINDEX(substr, str, start)`	Search from start position	`CHARINDEX('bc', 'abcabc', 3)`	`5`
`PATINDEX('%pattern%', str)`	Position of pattern (LIKE wildcards)	`PATINDEX('%[0-9]%', 'abc123')`	`4`

Concatenation

Method	Description	Example	Result
`+`	Concatenate (NULL propagates)	`'abc' + 'def'`	`'abcdef'`
`CONCAT(args)`	Concatenate (NULL treated as empty string)	`CONCAT('a', NULL, 'b')`	`'ab'`
`CONCAT_WS(sep, args)`	Concatenate with separator, skipping NULLs (2017+)	`CONCAT_WS('-', 'a', NULL, 'b')`	`'a-b'`
`STRING_AGG(col, sep)`	Aggregate strings (like GROUP_CONCAT)	`STRING_AGG(name, ', ')`	`'Alice, Bob'`

NULL trap: 'a' + NULL returns NULL. Use CONCAT or ISNULL(col, '') to handle NULLs safely.

Replace & Remove

Function	Description	Example	Result
`REPLACE(str, from, to)`	Replace all occurrences	`REPLACE('aabbaa', 'aa', 'X')`	`'XbbX'`
`STUFF(str, start, len, insert)`	Delete len chars at start and insert	`STUFF('abcdef', 2, 3, 'XY')`	`'aXYef'`
`TRANSLATE(str, from, to)`	Replace characters one-for-one (2017+)	`TRANSLATE('2-4-7', '-', '/')`	`'2/4/7'`

Padding & Formatting

Function	Description	Example	Result
`REPLICATE(str, n)`	Repeat string n times	`REPLICATE('ab', 3)`	`'ababab'`
`SPACE(n)`	String of n spaces	`SPACE(3)`	`' '`
`FORMAT(val, fmt)`	Format number/date as string	`FORMAT(1234.5, 'N2')`	`'1,234.50'`
`RIGHT(REPLICATE('0',5)+CAST(n AS VARCHAR),5)`	Zero-pad integer (classic trick)	n=42	`'00042'`

Type Conversion

Function	Description	Example	Result
`CAST(val AS type)`	Standard type cast	`CAST(3.14 AS VARCHAR(10))`	`'3.14'`
`CONVERT(type, val, style)`	Convert with optional style code	`CONVERT(VARCHAR, 3.14)`	`'3.14'`
`TRY_CAST(val AS type)`	Returns NULL instead of error on failure	`TRY_CAST('abc' AS INT)`	`NULL`
`STR(num, len, dec)`	Numeric to right-aligned string	`STR(3.14159, 8, 2)`	`' 3.14'`

Character Codes & Unicode

Function	Description	Example	Result
`ASCII(str)`	ASCII code of first character	`ASCII('A')`	`65`
`UNICODE(str)`	Unicode code point of first character	`UNICODE(N'€')`	`8364`
`CHAR(n)`	Character from ASCII code	`CHAR(65)`	`'A'`
`NCHAR(n)`	Character from Unicode code point	`NCHAR(8364)`	`N'€'`
`SOUNDEX(str)`	Phonetic code	`SOUNDEX('Smith')`	`'S530'`
`DIFFERENCE(s1, s2)`	Similarity of SOUNDEX (0–4)	`DIFFERENCE('Smith','Smythe')`	`4`

Common Patterns

-- Extract filename from full path
SELECT RIGHT(path, CHARINDEX('\', REVERSE(path)) - 1) AS filename

-- Extract text between two delimiters
SELECT SUBSTRING(str,
         CHARINDEX('[', str) + 1,
         CHARINDEX(']', str) - CHARINDEX('[', str) - 1)

-- Remove all non-numeric characters (requires loop or CLR; classic trick)
-- SQL Server 2017+ with TRANSLATE:
SELECT TRANSLATE(str, '0123456789', REPLICATE('#',10))  -- marks digits
-- Full removal requires a function or recursive CTE

-- Pad left with zeros to 6 digits
SELECT RIGHT('000000' + CAST(id AS VARCHAR), 6)

-- Check if value is numeric
SELECT CASE WHEN TRY_CAST(col AS FLOAT) IS NOT NULL THEN 1 ELSE 0 END

-- Aggregate into comma-separated list
SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name)
FROM employees