SQL Server 2019 String Functions: Uses and Examples

Formulas & functions · Updated November 22, 2026

SQL Server 2019 includes string functions for joining, searching, trimming, measuring, changing, and extracting text. Common choices include CONCAT and STRING_AGG for combining values, TRIM and REPLACE for cleanup, and CHARINDEX, PATINDEX, and SUBSTRING for finding and extracting text.

What counts as a string function?

String functions operate on character data, typically values stored in char, varchar, nchar, or nvarchar columns. SQL Server 2019 supports the familiar functions used in older releases as well as functions added in prior versions, including CONCAT_WS, STRING_AGG, TRANSLATE, and TRIM. These were introduced before SQL Server 2019, but they are available in SQL Server 2019.

There is no single SQL Server command that lists every string function in a practical, categorized way. It helps to think in terms of the job to do: combine values, normalize text, locate a pattern, extract part of a value, or format output. Some useful related functions are not strictly scalar string functions. For instance, STRING_SPLIT returns rows rather than one string, so it is a table-valued function.

Join strings and aggregate values

For combining a few expressions into one value, CONCAT is usually the clearest option. It converts its arguments to strings and treats NULL arguments as empty strings. This differs from the plus operator, whose handling of NULL depends on the expression and session or database settings.

SELECT CONCAT(N'Ada', N' ', N'Lovelace') AS FullName;

The result is Ada Lovelace. CONCAT_WS means concatenate with separator. It inserts the separator between non-NULL values and skips NULL values, which is handy for optional address fields or names:

SELECT CONCAT_WS(N', ', N'Paris', NULL, N'France') AS Place;

The result is Paris, France. An empty string is not the same thing as NULL, however; an empty argument can still produce a separator. If exact punctuation matters, decide how empty values should be handled before concatenating.

To combine values from multiple rows, use STRING_AGG. SQL Server 2019 supports it, including an optional ordering clause. The input expression determines the output type, so cast it to a sufficiently large type when the aggregated result could be long.

SELECT DepartmentID,
       STRING_AGG(CAST(EmployeeName AS nvarchar(max)), N', ')
           WITHIN GROUP (ORDER BY EmployeeName) AS Employees
FROM dbo.Employee
GROUP BY DepartmentID;

The ordering clause controls the order of names within each department’s result. Without it, do not rely on a particular sequence. As with other aggregates, NULL input values are omitted. A separator is added only between included values.

Clean and transform text

For removing spaces at the edges of a value, SQL Server 2019 supports TRIM, which removes the ordinary space character from both ends by default. LTRIM removes leading spaces and RTRIM removes trailing spaces. These operations do not remove every character that might look like whitespace, such as tabs or nonbreaking spaces.

SELECT TRIM(N'  Northwind  ') AS CleanName,
       LTRIM(N'  Northwind') AS LeftClean,
       RTRIM(N'Northwind  ') AS RightClean;

SQL Server 2019 also allows a set of characters to be specified in TRIM. For example, TRIM(N' .-' FROM N'--.Report. -') removes any of the listed characters from both ends; it does not remove them from the middle. The LEADING, TRAILING, and BOTH keywords for selecting a trim direction are a SQL Server 2022 feature at compatibility level 160, not SQL Server 2019 syntax.

REPLACE substitutes every occurrence of a search string, while TRANSLATE replaces characters position by position. For example, REPLACE(N'2026/11/03', N'/', N'-') returns 2026-11-03. Use REPLACE when the target is a sequence of characters; use TRANSLATE for a one-to-one mapping between sets of individual characters. The two character lists given to TRANSLATE must have the same number of characters.

Other transformation functions include UPPER and LOWER, which change letter case according to collation rules, and REVERSE, which reverses a string. STUFF removes a specified section and inserts replacement text at a position. REPLICATE repeats text, and SPACE creates a string of spaces. These can be useful for controlled formatting, though presentation-specific formatting is often better handled by the application that displays the data.

Find text and extract a part

CHARINDEX finds the starting position of one string inside another. Positions are one-based, and a result of zero means there was no match. It can also accept a starting position as a third argument. PATINDEX searches using a pattern with SQL Server’s wildcard syntax, making it useful when the location depends on a character class rather than a fixed substring.

DECLARE @Email nvarchar(100) = N'reader@example.com';

SELECT CHARINDEX(N'@', @Email) AS AtPosition,
       SUBSTRING(@Email, 1, CHARINDEX(N'@', @Email) - 1) AS UserName;

Here, AtPosition is 7 and UserName is reader. This example assumes the address contains an at sign. In production queries, guard against a missing delimiter before using its position to calculate a substring length; otherwise, the expression can produce an invalid length.

SUBSTRING returns a portion of a string from a starting position for a specified length. LEFT and RIGHT return a requested number of characters from either end. These are useful for fixed-format identifiers, but they do not validate that a value follows the expected format.

SELECT LEFT(N'INV-48291', 3) AS Prefix,
       RIGHT(N'INV-48291', 5) AS NumberPart,
       SUBSTRING(N'INV-48291', 5, 2) AS FirstDigits;

The outputs are INV, 48291, and 48. For pattern-based parsing, PATINDEX accepts percent signs and bracket expressions, for example a pattern that identifies the first character outside a chosen range. Remember that pattern matching and comparisons can be affected by the collation of the input.

Measure strings and understand Unicode

LEN returns the number of characters, but excludes trailing spaces. This is a frequent source of surprising results when checking padded values. DATALENGTH returns the number of bytes used to represent an expression, including trailing spaces; it is not a character count. For example, under common Unicode storage, LEN(N'cat ') returns 3, while DATALENGTH(N'cat ') returns 8 because nvarchar uses two bytes per ordinary character in that example.

Use LEN when the question is how many characters are meaningful for the operation, and DATALENGTH when checking storage size or whether trailing bytes are present. Byte counts vary with data type and encoding. SQL Server 2019 supports UTF-8 collations for varchar; therefore, a byte count should not generally be assumed to equal the number of characters.

For Unicode code points, UNICODE returns the integer value of the first character, and NCHAR returns the Unicode character for an integer value. ASCII and CHAR provide corresponding functions for code-page characters. With supplementary Unicode characters, behavior for counting and positions depends on whether the expression uses a supplementary-character-aware (SC) collation. If text includes emoji or characters outside the basic multilingual plane, test operations under the actual column collation rather than assuming every visible symbol occupies one position in every context.

Choose a function for the job

A practical choice is to use the function that states the intended operation most directly. For a fixed separator, use CHARINDEX; for wildcard pattern matching, use PATINDEX; for a known slice, use SUBSTRING, LEFT, or RIGHT. For cleanup, use TRIM for edges and REPLACE for occurrences throughout the value. For row-wise assembly, consider CONCAT or CONCAT_WS; for combining a group of rows, consider STRING_AGG.

  • Use QUOTENAME to delimit a SQL Server identifier, such as a table or column name, in dynamic SQL. It is not a substitute for parameterizing data values. Its input is limited to 128 characters, and longer input returns NULL.
  • Use STRING_ESCAPE(value, 'json') when escaping text for JSON string content. It supports JSON escaping; it is not a general-purpose HTML, URL, or SQL escaping function.
  • Use FORMAT for culture-aware presentation formatting when needed, but consider whether formatting belongs in the application. For large query workloads, simpler conversions can be preferable because FORMAT is typically more costly.
  • Use SOUNDEX and DIFFERENCE for phonetic comparisons, not as reliable identity matching. They can classify different names alike and are not substitutes for carefully designed search logic.

STR converts a numeric value to character text with a specified width and decimal places; it is primarily a formatting utility and can return asterisks if the requested width is too small. QUOTENAME, meanwhile, adds delimiters around an identifier and escapes closing delimiters. Avoid using string concatenation to insert untrusted values into executable SQL: use parameters for values, and use validated, quoted identifiers only where dynamic object names are unavoidable.

Common pitfalls and compatibility notes

One common misconception is that all functions treating a value as text behave the same around NULL, spaces, and collation. They do not. CONCAT turns NULL arguments into empty strings, while STRING_AGG skips NULL inputs. LEN omits trailing spaces, while DATALENGTH includes their bytes. CHARINDEX and PATINDEX return zero when they do not find a match. Make these cases explicit in tests and in expressions that calculate positions or lengths.

String comparisons and searches can be case-sensitive or case-insensitive, accent-sensitive or accent-insensitive, depending on collation. The collation may come from a column, an expression, or an explicit COLLATE clause. If results differ between environments, check the database and column collations before assuming a function is inconsistent. Converting types can also change behavior: mixing varchar and nvarchar may affect the resulting type, conversion, and byte length.

Most built-in functions are available without a special database compatibility setting, but some related features do have requirements. STRING_SPLIT is available from SQL Server 2016 when the database compatibility level is at least 130; it is a table-valued function, not a scalar string function. SQL Server 2019’s STRING_SPLIT does not provide the ordinal output option added in SQL Server 2022, and the returned row order should not be treated as guaranteed. If order matters, represent and preserve it explicitly.

For performance, avoid applying a function to an indexed column in a filtering predicate when a direct range or suitable computed-column strategy can express the condition. For example, searching with a leading wildcard such as LIKE '%text' generally cannot seek efficiently through an ordinary index on the column. Large-scale cleanup is often best done once when data is loaded or corrected, rather than repeatedly in every report query. Check execution plans and representative data before optimizing by assumption.

Frequently Asked Questions

Does SQL Server 2019 have a TRIM function?
Yes. SQL Server 2019 supports TRIM to remove spaces from both ends by default, and it supports specifying characters to remove. The directional LEADING, TRAILING, and BOTH syntax belongs to SQL Server 2022, not 2019.

What is the difference between LEN and DATALENGTH?
LEN counts characters but ignores trailing spaces. DATALENGTH counts bytes used by the expression and includes trailing spaces. Use the function that matches whether you need a character count or a storage-size measurement.

How do I concatenate strings in SQL Server 2019?
Use CONCAT for a few values and CONCAT_WS when a separator should appear between non-NULL values. For values from multiple rows, use STRING_AGG, adding WITHIN GROUP (ORDER BY ...) when a defined output order is required.

How can I find a substring in SQL Server?
Use CHARINDEX for an exact substring and check for a zero result to detect no match. Use PATINDEX when SQL wildcard patterns are needed. Both return a position rather than the matching text itself.

Is STRING_SPLIT a string function in SQL Server 2019?
It is a related built-in table-valued function: it splits a string into rows rather than returning one scalar string. It requires compatibility level 130 or higher, and SQL Server 2019 does not include the ordinal column option introduced later.

Does SQL Server 2019 support STRING_AGG?
Yes. STRING_AGG combines values from multiple rows into one string. NULL values are skipped, and an ordering clause can specify the sequence of the aggregated values.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.