SQL Server string functions let you inspect, search, extract, combine, replace, and format text in queries. The most useful include LEN, SUBSTRING, CHARINDEX, CONCAT, REPLACE, TRIM, and STRING_SPLIT; which one to use depends on whether you are cleaning data, finding a value, or changing how text is displayed.
What string functions do
A string is text stored in a character data type such as char, varchar, nchar, or nvarchar. String functions return a value derived from that text. Some return another string, such as UPPER; others return a number, such as LEN or CHARINDEX. They can appear in a SELECT list, a WHERE condition, an ORDER BY clause, or a data-modification statement.
Functions are not all interchangeable. LEFT extracts a fixed number of characters from the beginning, while CHARINDEX finds the position of a substring. REPLACE changes matching text throughout a value; STUFF removes a specified span and inserts replacement text at a position. Knowing the distinction helps avoid complicated expressions and accidental changes to data.
Choose the right string data type
Before applying functions, consider how the column is stored. varchar stores non-Unicode text using a code page, whereas nvarchar stores Unicode text. For multilingual text or characters that may not fit the column’s code page, nvarchar is generally the safer choice. Prefix Unicode string literals with N, as in N'東京', so SQL Server treats the literal as Unicode.
Fixed-width types, char and nchar, are padded to their declared length. Variable-width varchar and nvarchar do not normally add that padding. This matters when comparing values or measuring them: a padded column can contain trailing spaces even when the visible text looks shorter. Collation also matters. A column’s collation determines rules such as case and accent sensitivity for comparisons and many text searches, so a search for one spelling may or may not match another depending on the collation.
Measure and extract text
LEN(expression) returns the number of characters in an expression, but it does not count trailing spaces. DATALENGTH(expression) instead reports the number of bytes used to represent the value. It includes trailing spaces, and the byte count varies with the data type and encoding. For example, an nvarchar value commonly uses more bytes per character than a varchar value, though Unicode compression and supplementary characters complicate simple assumptions.
To extract text, use LEFT(expression, number) or RIGHT(expression, number) for a fixed count from either end. Use SUBSTRING(expression, start, length) when the starting position matters. Positions are one-based, so a start of 1 refers to the first character. Here is a basic example:
SELECT
LEFT('SQL Server', 3) AS FirstThree,
RIGHT('SQL Server', 6) AS LastSix,
SUBSTRING('SQL Server', 5, 6) AS FromPositionFive;
The results are SQL, Server, and Server, respectively. With supplementary Unicode characters, the way a function counts characters can depend on whether the expression uses a supplementary-character-aware collation. Do not assume that a displayed symbol always corresponds to one storage unit or byte.
Find text and replace it
CHARINDEX(search_text, expression [, start_location]) returns the position where a substring first occurs, or 0 if it is not found. It is useful for locating a delimiter before extracting a portion of a value. For example, CHARINDEX('@', EmailAddress) can help identify where an email address contains its at-sign, but it does not validate whether the address is valid.
PATINDEX('%pattern%', expression) searches for a pattern using SQL Server’s limited wildcard syntax, such as % for any sequence and _ for one character. It is not the same as a regular-expression search. Both CHARINDEX and PATINDEX return positions based on the expression and collation; a missing match returns 0.
For replacement, REPLACE(expression, search_text, replacement_text) replaces every occurrence of the specified text. TRANSLATE(expression, characters, translations), available in SQL Server 2017 and later, maps individual characters in one set to corresponding characters in another set. It is useful for one-character-for-one-character substitutions, not for replacing whole words. For example:
SELECT REPLACE('555.010.2040', '.', '-') AS NormalizedNumber,
TRANSLATE('ab-cd', 'abcd', 'ABCD') AS UpperLetters;
The results are 555-010-2040 and AB-CD. If you need to remove or insert a span by position, use STUFF(expression, start, length, replacement). For instance, it can replace a known portion of a string, but it is not a general-purpose pattern replacement tool.
Clean and standardize values
UPPER and LOWER change letter case according to the relevant language and collation rules. They can help standardize display or comparison values, but applying a function to a column in a filter can make it harder for SQL Server to use an index efficiently. When possible, use an appropriate collation or store a normalized value deliberately rather than wrapping every search column in a function.
LTRIM removes leading spaces and RTRIM removes trailing spaces. TRIM, available in SQL Server 2017 and later, removes leading and trailing space characters by default. It does not mean “remove every kind of whitespace”: tabs and line breaks are not automatically equivalent to ordinary spaces. SQL Server 2022 adds LEADING, TRAILING, and BOTH options for TRIM when the database compatibility level is 160. Check the server version and compatibility level before relying on that syntax.
When a column may contain NULL, remember that many string functions return NULL if an input argument is NULL. For a cleanup expression, decide whether NULL should remain unknown or be treated as empty text. Use COALESCE or ISNULL explicitly if converting it is appropriate; doing so changes the meaning of the data, not just its appearance.
Combine strings and build lists
The + operator can concatenate strings, but NULL behavior and implicit type conversion can make it surprising. CONCAT(value1, value2, ...) converts its arguments to strings and treats NULL arguments as empty strings, which makes it convenient for constructing output. CONCAT_WS(separator, value1, value2, ...), available in SQL Server 2017 and later, joins values with a separator and skips NULL values. It does not skip empty strings, so an empty value can still produce an extra separator.
For example, if FirstName is Ada and LastName is NULL, CONCAT(FirstName, ' ', LastName) produces Ada , including the literal space. CONCAT_WS(' ', FirstName, LastName) produces Ada, because it omits the NULL argument. Test the behavior with the actual NULL and empty-string cases in your data.
To aggregate values from multiple rows into one delimited string, use STRING_AGG(expression, separator), available in SQL Server 2017 and later. It ignores NULL input values and does not add separators for them. If output order matters, specify it with WITHIN GROUP (ORDER BY ...); without an ordering clause, the concatenation order is not guaranteed. Use it for reporting or presentation, not as a substitute for keeping relational values in separate rows when you need to query them individually.
Split delimited text
STRING_SPLIT(string, separator) turns a delimited string into rows. It is available in SQL Server 2016 and later, with database compatibility level 130 or higher. The separator is a single character, not an arbitrary multi-character string. Consecutive separators can yield empty values, and the output order is not guaranteed by default. If row order is important, do not infer it from the order in which results happen to appear.
SQL Server 2022 adds an optional third argument to request an ordinal column, subject to the applicable compatibility requirements. That ordinal can be used to restore input order with an explicit ORDER BY. For example, on a supported SQL Server 2022 database:
SELECT value, ordinal
FROM STRING_SPLIT('red,green,blue', ',', 1)
ORDER BY ordinal;
This returns the tokens in their original sequence. For older versions, or when delimiters can contain multiple characters, use a different splitting approach or parse the input before it reaches SQL Server. A delimited string is often a sign that the underlying data could instead be stored as one value per row.
Common problems and performance considerations
- Trailing spaces:
LENomits them, whileDATALENGTHincludes their bytes. Choose the measurement that answers your question. - NULL versus empty text: NULL means a missing or unknown value; an empty string is a present value with no characters. Functions such as
CONCATcan make them look alike in a result. - Implicit conversion: Mixing numbers, dates,
varchar, andnvarcharcan trigger conversions or affect result types. Convert deliberately when output format matters. - Truncation: A result can be shorter than expected if an expression has a bounded type. In particular,
REPLACEcan truncate results at 8,000 bytes unless its input expression is a large-value type such asvarchar(max)ornvarchar(max). - Filtering with functions: A condition such as
WHERE UPPER(Name) = 'ADA'may prevent an ordinary index onNamefrom being used as a direct seek. Consider the column collation, a persisted computed column, or a different search design for large tables.
Also distinguish string operations from formatting for display. SQL Server’s FORMAT can produce culture-aware text from numbers and dates, but it is not a general text-cleaning function and may be slower than simpler conversions in large queries. Prefer the simplest operation that expresses the required transformation, and check execution plans when a string expression is part of a performance-sensitive query.
Frequently Asked Questions
What is the difference between LEN and DATALENGTH?LEN counts characters but excludes trailing spaces. DATALENGTH returns the number of bytes used, including trailing spaces. Use LEN for a character count and DATALENGTH when storage length matters.
How do I get text before a delimiter in SQL Server?
Find the delimiter with CHARINDEX, then use LEFT or SUBSTRING to extract the preceding text. Check for a zero result from CHARINDEX first, because that means the delimiter was not found.
Does STRING_SPLIT preserve the original order?
Not by default. SQL Server 2022 can return an ordinal when its optional third argument is enabled, allowing you to order the results explicitly. On earlier versions, use another method if sequence is essential.
Why does TRIM not remove tabs or line breaks?
Its default behavior removes ordinary space characters at the ends of a string, not every whitespace character. If tabs or line breaks must be removed, specify the characters to trim where supported or explicitly replace those characters.
Which function combines values while skipping NULLs?
Use CONCAT_WS to combine expressions with a separator while omitting NULL arguments, or STRING_AGG to combine values from multiple rows. Both are available in SQL Server 2017 and later.
How to choose a function
Start with the shape of the task: use LEN or DATALENGTH to measure, LEFT, RIGHT, or SUBSTRING to extract, CHARINDEX or PATINDEX to locate, and REPLACE or STUFF to modify text. Choose CONCAT or CONCAT_WS for values in one row, STRING_AGG for values across rows, and STRING_SPLIT when delimited input must become rows. Finally, verify NULL behavior, data type, collation, SQL Server version, and ordering requirements. Those details often determine whether an expression is correct for production data.
Related
- Normal Balance in Accounting: How Debits and Credits Work
- Balance of Payments Mind Map: Accounts and Flows
- Split a Part in Onshape: Steps, Uses, and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Attendance Sheet With Overtime: Setup and FormulasYes. An Excel attendance sheet can calculate shift hours and overtime from employees’ start times, end times, and…
- Sort Dates in Excel in Ascending Order the Right WayTo sort dates in Excel from oldest to newest, select a date cell, then choose Data > Sort…
- How to Get a Sequence Number in Excel AutomaticallyTo create a simple sequence number in Excel, enter a formula in the first numbering cell and fill…
- Budget Spreadsheet Sample: Monthly Template and FormulasA useful budget spreadsheet sample has columns for each category’s planned amount, actual amount, and difference, plus totals…
- LEN SUBSTITUTE Excel Formula: Count Characters or TextTo count how many times a character appears in a cell, subtract the length of the text after…
- COUNTIF Formula for Duplicates in Excel: Find and FlagTo flag every value that appears more than once in Excel, enter =COUNTIF($A$2:$A$100,A2)>1 in a helper column beside…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.