SQL Server string functions let you measure, search, extract, combine, replace, and format text in a query. Common choices include LEN, SUBSTRING, CHARINDEX, REPLACE, CONCAT, and TRIM; the right one depends on whether you are working with positions, patterns, missing values, or text cleanup.
What SQL Server string functions do
A string function takes text as input and returns text, a number, or another value derived from that text. You can use one in a SELECT list to display a transformed value, in a WHERE clause to filter rows, or inside a larger expression. For example, LEN(CustomerName) returns a length, while LEFT(CustomerName, 4) returns the first four characters.
SQL Server has functions for several common jobs:
- Measure text:
LENandDATALENGTH. - Find text:
CHARINDEXandPATINDEX. - Extract text:
LEFT,RIGHT, andSUBSTRING. - Change text:
REPLACE,STUFF,TRANSLATE, and trimming functions. - Build or split text:
CONCAT,CONCAT_WS,STRING_AGG, andSTRING_SPLIT.
These functions do not change the stored value unless you use their results in a data-changing statement such as UPDATE. A query that selects a cleaned version of a name, for instance, does not clean the table itself.
Measure strings: LEN and DATALENGTH
LEN(expression) returns the number of characters, but it does not count trailing ordinary spaces. Thus, a value containing the characters cat followed by spaces can have a LEN result of 3. This behavior often matters when checking fixed-width data or diagnosing values that look identical on screen.
DATALENGTH(expression) returns the number of bytes used to represent the expression, including trailing spaces. It is not a character count: byte use depends on the data type and encoding. For example, nvarchar commonly uses two bytes per code unit, while varchar storage depends on its collation and encoding. Supplementary Unicode characters and collations can affect how character positions and counts behave, so test with the actual column type and collation when exact counts matter.
For a quick comparison, with a value stored in a column called Label, query SELECT LEN(Label), DATALENGTH(Label) FROM dbo.Items;. If the results differ more than expected, check trailing spaces, the column type, and the collation rather than assuming one function is wrong.
Find and extract text
CHARINDEX(search_expression, expression [, start_location]) finds a literal sequence and returns its one-based starting position. It returns 0 when the sequence is not found, and returns NULL if an input is NULL. An optional starting location lets a search continue later in the string. For example, CHARINDEX('@', EmailAddress) locates the at-sign in an email address; it does not validate that the address is correctly formed.
PATINDEX('%pattern%', expression) searches using SQL Server’s pattern wildcards, such as % for any sequence and _ for one character. Unlike CHARINDEX, its pattern must be enclosed in percent signs to find a match anywhere. Use it when a pattern is useful, not as a substitute for a regular-expression engine: its wildcard rules are limited and are not regular expressions.
To return part of a value, use LEFT(expression, count) or RIGHT(expression, count) for characters from an end, and SUBSTRING(expression, start, length) for a section at a specified position. Positions are one-based. A common extraction might be SUBSTRING(PhoneNumber, 1, 3) to return an area-code-like prefix, assuming the source has a consistent format. For real data, first consider punctuation, country codes, and inconsistent spacing.
Search results need a condition when absence is possible. For example, if an email address has no at-sign, CHARINDEX('@', EmailAddress) returns 0. Adding 1 and passing that to SUBSTRING would start at the first character, not produce a meaningful domain. Check for a positive position before extracting, and decide how invalid or missing input should be represented.
Trim, replace, and transform characters
LTRIM removes leading spaces and RTRIM removes trailing spaces. TRIM, available in SQL Server 2017 and later, removes spaces from both ends by default. For instance, TRIM(CustomerName) is useful when imported data has accidental outer spaces. On older SQL Server versions, the familiar equivalent is LTRIM(RTRIM(CustomerName)).
In SQL Server 2022, TRIM also supports options such as LEADING, TRAILING, and BOTH, subject to the database compatibility level required for that syntax. Do not assume the expanded form will work on an older server or a database using an earlier compatibility level. The basic function and the newer positional options have different version requirements.
REPLACE(expression, search, replacement) replaces every occurrence of a literal search string. It is useful for simple substitutions, such as replacing hyphens with spaces, but it does not understand words or token boundaries. The comparison follows the expression’s collation, which can affect case sensitivity. Also, a result may be limited to 8,000 bytes unless the input is a large-value type such as varchar(max) or nvarchar(max); cast appropriately when a long result must be preserved.
STUFF(expression, start, length, replacement) removes a section and inserts replacement text at that position. It can replace a known span, while REPLACE substitutes every matching occurrence. SQL Server 2017 introduced TRANSLATE, which maps individual characters to corresponding characters in another set. It is suited to one-character-for-one-character conversions, not replacing multi-character words.
Combine strings and handle NULL values
The + operator can concatenate character values, but its behavior with NULL is a frequent source of surprises. In current SQL Server versions, CONCAT_NULL_YIELDS_NULL is always on, so adding a NULL value makes the combined result NULL. CONCAT, available from SQL Server 2012, instead converts NULL arguments to empty strings and converts other supported values to text.
For example, CONCAT(FirstName, ' ', LastName) avoids a null result if one name component is missing. It can still leave an extra space, so trim or conditionally assemble the result if presentation matters. CONCAT_WS(separator, value1, value2, ...), also available from SQL Server 2017, places a separator between values and skips NULL arguments. It does not skip empty strings, which is worth checking when data contains both empty and missing values.
For many rows, STRING_AGG(expression, separator) combines values into one delimited string and is available in SQL Server 2017 and later. It can include WITHIN GROUP (ORDER BY ...) to specify ordering. Without an explicit ordering clause, do not depend on a particular concatenation order. Aggregated output can also be affected by the input expression’s data type and length.
Split strings into rows
STRING_SPLIT(string, separator) separates a delimited string into rows. It is available in SQL Server 2016 and later, with database compatibility requirements. It is helpful for simple lists, but it does not automatically trim values, validate them, or treat quoted separators as CSV syntax. A comma inside a quoted field is not protected just because the input resembles a CSV file.
Output order is not guaranteed unless you request and use an ordinal. SQL Server 2022 supports an optional third argument that enables an ordinal column; order the final query by that column if the original sequence matters. Earlier versions do not provide that option through STRING_SPLIT. Always verify the server version and compatibility level, and consider a proper table-valued input or structured format when the list represents important application data.
Unicode, collations, and type choices
Use nvarchar or nchar when a column must store Unicode text across a broad range of languages. Unicode string literals are commonly prefixed with N, as in N'東京', to make their intended type explicit. varchar can also support extensive character sets with modern UTF-8 collations, but its behavior depends on the chosen collation and SQL Server configuration.
A collation determines rules for comparing and sorting text, including aspects such as case and accent sensitivity. It can therefore influence whether searches and replacements match particular characters. Functions that return positions work with the character interpretation used by SQL Server and the relevant collation; grapheme clusters that people see as one displayed symbol may involve multiple code points. If a position is used to edit international text, test representative characters instead of assuming every visible symbol occupies one simple unit.
NULL is also distinct from an empty string. An empty string contains zero characters, while NULL means the value is absent or unknown. Functions commonly propagate NULL, though functions such as CONCAT have specific null-handling behavior. Use IS NULL to test for missing values; comparing a value to the text 'NULL' or to an empty string is not the same test.
Choosing functions and avoiding performance problems
Start with the shape of the task: use CHARINDEX for a literal search, PATINDEX for wildcard patterns, SUBSTRING for a known position and length, and REPLACE for a literal substitution throughout a value. Use CONCAT or CONCAT_WS when predictable null handling is more important than using +. For formatting values as dates or numbers, explicit conversions such as CAST or CONVERT are often clearer than treating everything as a string operation.
Be cautious about applying a function to an indexed column in a filter. A condition such as WHERE LEFT(ProductCode, 2) = 'AB' may make it harder for SQL Server to seek efficiently through an ordinary index on ProductCode. When the goal is to match a prefix, a condition such as WHERE ProductCode LIKE 'AB%' may allow an index seek, depending on the data, collation, and execution plan. Check the actual execution plan and measure with representative data; no rewrite guarantees the same performance in every schema.
For repeated cleanup or search logic, consider whether the normalized value should be stored or exposed through a computed column, and whether indexing it is appropriate. That choice adds maintenance and storage considerations, so it is not automatically better than calculating a value in a query. Avoid using string operations to conceal poor data modeling: if a list of values needs frequent filtering or joining, storing each item as a separate related row is often more reliable than repeatedly splitting a delimited field.
Frequently Asked Questions
What is the difference between LEN and DATALENGTH in SQL Server?LEN counts characters but ignores trailing spaces. DATALENGTH returns bytes, including trailing spaces, and the byte count depends on the type and encoding. Use the one that matches whether you need a character count or storage size.
How do I get text after a character in SQL Server?
Find the character with CHARINDEX, then pass the position after it to SUBSTRING. Check that the search returned a positive position first; otherwise, the delimiter was not found and the extraction start may not mean what you intend.
Does SQL Server have a TRIM function?
Yes. Basic TRIM is available in SQL Server 2017 and later and removes spaces from both ends by default. SQL Server 2022 adds positional options such as LEADING and TRAILING, with a database compatibility-level requirement.
Does STRING_SPLIT preserve the original order?
Do not rely on its output order by default. SQL Server 2022 can return an ordinal when the optional enable-ordinal argument is used; sort by that column to restore sequence. Earlier versions do not offer that ordinal argument.
Which function should I use to join strings when values can be NULL?
Use CONCAT to treat null arguments as empty strings, or CONCAT_WS when you also want a separator and null arguments skipped. Remember that empty strings are not the same as NULL, and CONCAT_WS does not skip empty values.
Related
- Split String in JavaScript: Methods, Limits, and Examples
- How to Split in Onshape: Parts, Faces, and Sketches
- EF Core AsSplitQuery: How It Works and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIFS Won’t Work: Causes and FixesSUMIFS usually fails because its ranges do not line up, its criteria do not match the values in…
- Excel Data Validation INDIRECT for Dependent ListsYes. In Excel, INDIRECT can make a data-validation drop-down depend on another cell: name each child list after…
- Excel Named Ranges: Formulas That WorkYes, you can use formulas inside named ranges in Excel, but with a key distinction: a named range…
- Excel Filter by Named Range: What Works and HowYes—but Excel’s standard Filter dropdown does not accept a named range as a direct filter condition. You can…
- Excel Vertical Lookup: VLOOKUP, XLOOKUP, and TipsAn Excel vertical lookup searches down the first column of a table for a value, then returns related…
- Sort in Excel and Move Entire Rows Without Splitting DataTo sort in Excel and move entire rows together, select the full data range, then choose Data >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.