T-SQL string functions are built-in SQL Server tools for finding, extracting, changing, combining, measuring, and formatting text. Common choices include CONCAT to join values, SUBSTRING to extract characters, CHARINDEX to find text, and REPLACE to substitute it; the right function depends on whether you are transforming a value, searching it, or preparing it for display.
What T-SQL string functions do
T-SQL is the language used to query and manage data in Microsoft SQL Server. Its string functions operate on character data such as char, varchar, nchar, and nvarchar. They can be used in a SELECT list, a WHERE condition, an ORDER BY clause, or an expression that updates a column.
Some functions return text, while others return a number or a value of another type. For example, LEFT returns a piece of a string, LEN returns a character count, and CHARINDEX returns the position of a match. Knowing the return type and how the function treats nulls, spaces, and character encoding helps prevent unexpected results.
SQL Server has added functions over time, so availability can depend on the server version and, for some features, the database compatibility level. Core functions such as LEFT, RIGHT, and REPLACE are longstanding. CONCAT arrived in SQL Server 2012; STRING_SPLIT requires SQL Server 2016 or later and compatibility level 130 or higher; and STRING_AGG is available from SQL Server 2017.
Everyday functions for joining and extracting text
Use CONCAT when combining values, especially when any input might be null. It converts its arguments to strings and treats null arguments as empty strings. For example, SELECT CONCAT(N'Ada', N' ', N'Lovelace'); returns Ada Lovelace. By contrast, the + operator can produce a null result when a participating value is null, depending on SQL Server settings and expression behavior. CONCAT makes the intended null handling clearer.
CONCAT_WS, available in SQL Server 2017 and later, joins values using a separator and skips null arguments. For example, SELECT CONCAT_WS(N', ', N'North', NULL, N'West'); returns North, West. It does not skip empty strings, so an empty value can still create an extra separator.
For extraction, LEFT(expression, count) returns characters from the beginning, while RIGHT(expression, count) returns them from the end. SUBSTRING(expression, start, length) takes a portion beginning at a one-based position. For example, SELECT SUBSTRING(N'Order-483', 7, 3); returns 483. The requested length should be a nonnegative number; a start position before the beginning is treated as starting at the first character, while a start beyond the string returns an empty string.
STUFF(expression, start, length, replacement) removes a specified section and inserts replacement text at that position. It is useful when a known range must be replaced, whereas REPLACE targets every occurrence of a matching substring.
Finding, replacing, and trimming characters
CHARINDEX(search_expression, expression) finds the first occurrence of one string inside another and returns its one-based position. It returns zero when no match is found. For instance, SELECT CHARINDEX(N'-', N'Order-483'); returns 6. An optional start position lets a search begin later in the string. Matching behavior can be affected by the expression’s collation, including whether comparisons are case-sensitive.
PATINDEX searches for a pattern using SQL Server wildcard syntax, such as % for any sequence of characters and _ for one character. Unlike CHARINDEX, it is useful when the target is a pattern rather than a literal substring. For example, it can locate the first digit by searching for a pattern like %[0-9]%, though the returned position corresponds to the first match within that pattern. Patterns are not regular expressions.
REPLACE(expression, old_text, new_text) substitutes every occurrence of the old text. A common issue is its output size: when the input expression is not a large-value type, the result can be limited to 8,000 bytes. Cast the input to varchar(max) or nvarchar(max) when a longer result is required. TRANSLATE, introduced in SQL Server 2017, maps individual characters position by position; use it for one-to-one character substitutions, not for replacing a multi-character word.
For trimming, LTRIM removes leading ordinary spaces and RTRIM removes trailing ordinary spaces. TRIM, available from SQL Server 2017, removes specified characters from the ends of a string and removes spaces by default. SQL Server 2022 adds LEADING, TRAILING, and BOTH options when the database compatibility level is 160. These functions do not automatically remove every kind of whitespace, such as tabs or all Unicode space characters.
Splitting strings and aggregating values
STRING_SPLIT turns a delimited string into rows. For example, SELECT value FROM STRING_SPLIT(N'apple,pear,plum', N','); produces one row per item. It accepts a single-character separator, and its output order is not guaranteed unless you have an ordinal column available and explicitly order by it. SQL Server 2022 supports an optional third argument that enables ordinals; check the version and compatibility requirements for the target database before relying on it.
Splitting a delimited string is useful for importing simple lists or handling parameters, but it is not a substitute for storing repeating values in a properly normalized table. A delimiter can also appear inside real data, and a split operation does not by itself validate or convert the resulting values. Trim or validate each item as needed, and use TRY_CONVERT when converting potentially invalid text to a typed value.
STRING_AGG combines values from multiple rows into one string, optionally using a separator. It is available in SQL Server 2017 and later. For example, SELECT STRING_AGG(ProductName, N', ') FROM dbo.Products; returns a comma-separated list of product names. To make the order predictable, use the function’s WITHIN GROUP (ORDER BY ...) clause where supported. Null input values are omitted, and the separator is not added for those nulls. For large results, consider the expression’s data type and the result-size limits that apply.
Length, case, and character encoding
LEN returns the number of characters in a string but excludes trailing ordinary spaces. Thus, SELECT LEN(N'cat '); returns 3. DATALENGTH instead reports the number of bytes used to represent an expression and includes trailing spaces. It is not a character counter: an nvarchar value commonly uses more bytes per character than a varchar value, and supplementary Unicode characters require particular care when interpreting counts.
UPPER and LOWER change letter case according to the applicable collation. Case conversion is not a reliable way to establish a universal linguistic sort order or to validate identifiers across different languages. Comparisons and sorting also use collation rules, which can distinguish or ignore case and accents. If a query needs a specific comparison behavior, choose or apply an appropriate collation deliberately.
Use nvarchar and Unicode string literals with the N prefix, such as N'東京', when text may contain characters outside a code page supported by the database’s varchar encoding. Otherwise, characters can be lost or changed during conversion. Functions such as ASCII and CHAR are intended for character values in a code page; UNICODE and NCHAR are their Unicode-oriented counterparts.
Formatting and escaping text
Most database string functions are for data manipulation, not presentation. FORMAT can render a value using a .NET format pattern and culture, but it is generally more expensive than straightforward conversions and is not ideal for large-scale formatting in queries. Prefer storing dates and numbers in their native types, then format them in the application or reporting layer when possible. If SQL output must have a specific representation, test the format and culture explicitly.
STRING_ESCAPE(text, 'json') escapes characters for JSON string content. It does not create a complete JSON document, nor does it replace SQL parameterization. For dynamic SQL identifiers, QUOTENAME adds delimiters for a database identifier and is limited to inputs of up to 128 characters; it is not a general-purpose method for escaping arbitrary text values. Use parameters for data values in SQL statements rather than concatenating untrusted input.
Other useful helpers include REVERSE, which reverses a string, and REPLICATE, which repeats a string a requested number of times. SPACE returns a specified number of spaces. These can help with focused transformations, but using spaces to simulate fixed-width display is usually better left to a reporting or presentation layer.
Choosing a function and avoiding common problems
Start with the exact operation, then check its null behavior, indexing rules, data type, collation, and version requirements. A function may return the expected visible text while still causing conversion, comparison, or performance surprises.
- To combine possibly null values, prefer
CONCAT; useCONCAT_WSwhen a separator is needed and null inputs should be skipped. - To find a literal substring, use
CHARINDEX; usePATINDEXwhen SQL wildcard patterns are needed. - To extract by position, use
LEFT,RIGHT, orSUBSTRING; remember that positions start at one. - To replace all literal occurrences, use
REPLACE; useSTUFFto replace a range at a known position. - To turn delimited input into rows or combine rows into text, consider
STRING_SPLITorSTRING_AGG, while checking ordering and version requirements.
A frequent performance issue is wrapping an indexed column in a function in a filter, for example WHERE LOWER(CustomerName) = N'ada'. SQL Server may be unable to use a normal index seek efficiently because it must evaluate the expression for rows. When this matters, consider an appropriate collation, a computed column with an index, or a search design suited to the requirement. Do not assume that rewriting a query will improve it; inspect the actual execution plan and test with representative data.
Also distinguish string manipulation from data cleanup. Trimming a value in a query does not necessarily fix the stored data, and a search result can change when trailing spaces or collation rules are involved. Validate data at ingestion, define consistent column types and collations, and avoid converting a column repeatedly in joins or filters when a compatible schema can prevent the conversion.
Frequently Asked Questions
What is the difference between LEN and DATALENGTH in SQL Server?LEN counts characters but excludes trailing ordinary spaces. DATALENGTH returns bytes and includes trailing spaces, so it is useful for checking storage size rather than visible character count.
Does CHARINDEX return zero or NULL when text is not found?
For a non-null search that has no match, CHARINDEX returns zero. If an input expression is null, the result is null. Test for zero when checking whether a non-null string contains a target.
Does STRING_SPLIT preserve the original order?
Do not rely on its output order unless you use an ordinal column supported by your SQL Server version and explicitly sort by that ordinal. Relational query results have no guaranteed order without an ORDER BY clause.
How do I concatenate strings when one value is NULL?CONCAT treats null inputs as empty strings. For separator-based joining that skips null values, use CONCAT_WS on SQL Server 2017 or later. Be aware that an empty string is not the same as null.
Should I use FORMAT to create dates or numbers as text?
Use it when a culture-aware formatted string is specifically needed in SQL Server, but avoid it for large query workloads when simpler conversions or application-side formatting will do. Keep dates and numbers in native types for filtering, sorting, and calculations.
Related
- Can You Play Split Fiction on Mac?
- How to Compare Two Excel Sheets in Excel
- Java split() Method: Syntax, Examples, and Pitfalls
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Upper Quartile Formula in Excel: How to Find Q3 CorrectlyIn Excel, use =QUARTILE.INC(A2:A20,3) to find the upper quartile, or third quartile (Q3), of the numbers in A2:A20.…
- Excel COUNTIF Less Than a Cell: Formula and ExamplesTo count values in Excel that are less than a value in another cell, use =COUNTIF(B2:B20,"<"&D2). This assumes…
- AVERAGEIF in Excel: What It Returns and How to Use ItAVERAGEIF is not a number in Excel; it is a worksheet function that calculates the average of cells…
- Excel SUM and SUBTOTAL: Formulas, Filters, and ExamplesTo add a range in Excel, use =SUM(B2:B10). To add only the rows currently shown after filtering, use…
- Excel COUNTIF Not Text? Use COUNTIF with WildcardsTo count cells that do not contain text in Excel, use =COUNTIF(range,"*") — this counts numbers, dates, and…
- How to Shuffle Cells in Excel: Formulas and Safe StepsYes. Excel has no single Shuffle command, but you can randomize a list or whole rows by adding…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.