SQL Server string functions are built-in T-SQL tools for combining, searching, measuring, extracting, and changing text. Functions such as CONCAT, SUBSTRING, CHARINDEX, REPLACE, and TRIM let you work with text directly in a query, while the right choice depends on the data type, treatment of NULLs, and whether you need exact or pattern-based matching.
What SQL Server string functions do
A string is text stored in a character data type, most commonly varchar, nvarchar, char, or nchar. SQL Server string functions accept text expressions and return a value, a position, or a related result. They can appear in a SELECT list, a filter, a join condition, or other T-SQL expressions.
Some functions change what text contains; others report information about it. For example, UPPER returns an uppercase version, while LEN returns a length. A function does not normally modify the stored column by itself: an UPDATE statement is needed to save a changed value back to a table.
Here is a small query that combines several common operations:
SELECT
UPPER(LEFT('database', 4)) AS prefix,
LEN('database') AS character_count,
CHARINDEX('base', 'database') AS match_position;
It returns DATAB only if the selected length is five; with the query as written, LEFT returns DATAB? No: the first four characters of database are data, so prefix is DATA. The length is 8 and the match position is 5. SQL Server reports string positions starting at 1, not 0.
Common functions for changing and extracting text
These functions cover many everyday tasks:
UPPERandLOWERchange letter case. Results can be affected by the collation and language rules in use.LEFTandRIGHTreturn a specified number of characters from the beginning or end of a string.SUBSTRINGreturns part of a string from a starting position. In the traditional syntax, supply the expression, start, and length.REPLACEreplaces every occurrence of one string with another. For example,REPLACE('blue-green', '-', ' ')returnsblue green.STUFFremoves a range of characters and inserts replacement text at that position. It is useful when a replacement must be made by position rather than by matching a particular substring.REVERSEreturns the characters in reverse order. It is a transformation, not a reliable general-purpose way to parse complex text.
For example, with a value such as AB-1234, LEFT(value, 2) returns AB, and SUBSTRING(value, 4, 4) returns 1234. These expressions assume the format is consistent. If the separator can move or be absent, searching for it with CHARINDEX and handling missing matches explicitly is safer than assuming fixed positions.
Searching for text and patterns
CHARINDEX finds the starting position of one string inside another. It returns 0 when the search text is not found, and can take an optional starting location. A NULL input generally produces NULL. Use the result carefully when passing a position into another function: a zero result means there is no match, not that the match begins at the first character.
SELECT CHARINDEX('-', 'AB-1234') AS dash_position;
This returns 3. A query can use that location to extract the portion after the dash, provided it checks that the dash exists. PATINDEX also returns a position, but its search expression supports SQL Server pattern syntax, including wildcards such as % and _. It is not the same as full regular-expression search; T-SQL LIKE patterns have their own limited syntax.
For a straightforward literal search, choose CHARINDEX. Choose PATINDEX when the pattern itself matters. Collation affects whether string comparisons are case-sensitive or accent-sensitive, so the same search can behave differently under different collations. If a query needs a specific comparison rule, an explicit collation can be applied, but it should match the intended language and comparison behavior.
Combining strings and handling NULL
CONCAT joins values after converting them to strings, and treats a NULL argument as an empty string. CONCAT_WS joins values using a separator and skips NULL value arguments; the separator itself must not be NULL if you expect a normal joined result. CONCAT_WS was introduced in SQL Server 2017.
SELECT CONCAT('Order ', 42, ': ', 'ready') AS message,
CONCAT_WS(', ', 'Ava', NULL, 'Noah') AS names;
The results are Order 42: ready and Ava, Noah. By contrast, the plus operator has different NULL behavior: on SQL Server 2017 and later, concatenating a NULL with + yields NULL because CONCAT_NULL_YIELDS_NULL is always ON. Older versions and legacy settings can differ, which is one reason CONCAT is often clearer when a NULL should behave like empty text.
Be aware of result types and length limits when building long strings. SQL Server determines the output type from the arguments, and a result without a large-value input can be limited to a bounded string length. If long output is required, use an appropriate varchar(max) or nvarchar(max) input and verify the types of intermediate expressions as well. Do not assume that combining values automatically preserves unlimited length.
Trimming, splitting, and translating text
TRIM removes specified characters from the ends of a string; the basic form removes ordinary space characters at both ends. It does not remove every kind of whitespace by default. For example, tabs and some Unicode space characters may need to be handled separately. LTRIM and RTRIM remove leading and trailing spaces, respectively. Basic TRIM support was added in SQL Server 2017.
SQL Server 2022 adds LEADING, TRAILING, and BOTH options for TRIM, subject to database compatibility level 160. That version can also accept an optional character set to remove. Check the version and compatibility level before using newer syntax in a database that must run on older installations.
STRING_SPLIT turns a delimited string into rows. It is available in SQL Server 2016 and later when the database compatibility level is at least 130. The function is useful for occasional parsing, but a delimited list is often a poor substitute for storing related values in separate rows. Its output order is not guaranteed unless you use an ordering method supported by your version. SQL Server 2022 adds an optional ordinal column argument; to return values in source order, request ordinals and explicitly order by that column.
TRANSLATE, available from SQL Server 2017, replaces individual characters according to corresponding positions in two character strings. Unlike REPLACE, it is designed for one-to-one character substitution rather than replacing a multi-character substring. Choose it for character mapping, and choose REPLACE for phrase or substring replacement.
Length, Unicode, and character data types
LEN returns the number of characters in an expression but excludes trailing ordinary spaces. DATALENGTH instead reports the number of bytes used to represent the expression, including trailing spaces. These functions answer different questions: use LEN when you need a character count, and DATALENGTH when storage size in bytes is relevant.
Unicode text is commonly stored in nvarchar or nchar, and Unicode string literals are commonly prefixed with N, as in N'東京'. That prefix helps SQL Server interpret the literal as Unicode rather than first converting it through a non-Unicode code page. Modern collations can also support UTF-8 for varchar; storage behavior therefore depends on the data type and collation, not just the visible characters.
Do not assume one visible symbol always corresponds to one byte or one unit in every SQL operation. Supplementary Unicode characters, combining marks, and collation rules complicate character counting and comparison. When validating an input limit, define whether the requirement is a number of user-perceived characters, SQL character units, or bytes, then select and test the appropriate measurement. Fixed-width char and nchar values are padded, which can also affect comparisons and length-related expectations.
Choosing functions and avoiding performance problems
Pick a function based on the exact task rather than using a complicated expression by default:
- Join values while treating NULL as empty:
CONCAT. - Join fields with a delimiter while skipping NULL fields:
CONCAT_WS. - Find a literal substring:
CHARINDEX. - Find a wildcard pattern:
PATINDEX. - Extract by known position:
LEFT,RIGHT, orSUBSTRING. - Replace a matching substring:
REPLACE; map individual characters:TRANSLATE.
Applying a function to a column in a filter can make it harder for SQL Server to use a normal index seek. For example, filtering on WHERE LOWER(UserName) = 'sam' applies a function to each candidate value. Depending on the query and schema, storing normalized data, using an appropriate case-insensitive collation, or defining an indexed computed column may be better. Inspect the actual execution plan and test with representative data rather than assuming a function always causes a scan.
Leading-wildcard searches such as LIKE '%word%' also commonly require scanning because an ordinary index cannot readily seek to an unknown prefix. For large-scale linguistic or document search, SQL Server Full-Text Search may be a more suitable feature. It is not a direct replacement for every substring search, so confirm that its tokenization and matching behavior meet the requirement.
Common mistakes and safe use
A frequent mistake is confusing LEN with DATALENGTH, or expecting LEN to count trailing spaces. Another is using a zero result from CHARINDEX as if it were a valid position. Check the no-match case before calculating a substring, especially when input is inconsistent or user supplied.
String functions do not make dynamically assembled SQL safe. Avoid concatenating user-entered values into executable SQL text. Use parameterized queries for values. QUOTENAME can delimit a SQL identifier, such as a table or column name, but it is not a substitute for parameters and should not be used to protect arbitrary data values. Identifiers that must be dynamic should also be validated against an allowed list.
Finally, be deliberate about conversions. Implicit conversion between Unicode and non-Unicode data, different collations, or numbers and strings can change comparison behavior or add work to a query. Explicitly choose the intended data type and collation where needed, and test edge cases such as NULL, empty strings, trailing spaces, non-ASCII text, and missing delimiters.
Frequently Asked Questions
What is the most commonly used string function in SQL Server?
There is no single most useful function for every query. CONCAT, SUBSTRING, LEN, CHARINDEX, and REPLACE are common because they address different everyday needs: joining, extracting, measuring, searching, and changing text.
What is the difference between LEN and DATALENGTH?
LEN counts characters but excludes trailing spaces. DATALENGTH returns the number of bytes used by the expression and includes trailing spaces. Unicode encoding and collation can affect how many bytes a value occupies.
How do I find a word inside a SQL Server string?
Use CHARINDEX for a literal search and check whether the result is greater than 0. Use PATINDEX if you need SQL Server wildcard patterns. The comparison result may depend on the expression’s collation.
Does SQL Server have a string split function?
Yes. STRING_SPLIT returns rows from a delimited string in SQL Server 2016 and later, with database compatibility level 130 or higher. Do not rely on its row order unless you request and explicitly sort by ordinals where that option is available.
How do I concatenate strings when a value might be NULL?
Use CONCAT when NULL should act like an empty string. Use CONCAT_WS when joining several fields with a separator and skipping NULL fields. Test empty strings separately if they should also be omitted, because an empty string is not NULL.
Are SQL Server string functions case-sensitive?
It depends primarily on the collation used for the comparison. A case-insensitive collation generally treats upper- and lowercase versions as equal in searches, while a case-sensitive collation distinguishes them. Functions that transform case and operations that compare strings are related but not interchangeable.
Related
- Customizable Invoice Template in Excel: How to Make One
- Daily Attendance Register Template Excel: Setup and Tips
- Customer Details in Excel: How to Set Up a Useful List
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Not Duplicate in Excel: Count Unique ValuesTo count values that appear only once in Excel, use =SUMPRODUCT((A2:A10<>"")*(COUNTIF(A2:A10,A2:A10)=1)) when the values are in A2:A10; it…
- Basic Invoice Template Excel: Setup and FormulasA basic invoice template in Excel can be made with invoice and customer details, a line-item table, and…
- VLOOKUP Examples for Practice: Learn Excel Lookup SkillsFor a first VLOOKUP practice problem, put product IDs in A2:A6, prices in B2:B6, and the lookup ID…
- Microsoft Excel TRIM Function: Remove Extra SpacesMicrosoft Excel’s TRIM function removes leading and trailing ordinary spaces from text and reduces repeated spaces between words…
- Excel Insert Image from URL: Formula, Steps, and FixesTo insert an image from a URL in Microsoft Excel for Microsoft 365, put the direct image address…
- Excel SUMIF Between Dates: Formula and ExamplesTo sum values between two dates in Excel, use SUMIFS with a start-date criterion and an end-date criterion.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.