SQL Server’s built-in split function is STRING_SPLIT, which turns a delimited string into a table of values. Use it in a FROM clause, such as SELECT value FROM STRING_SPLIT('red,green,blue', ',');; SQL Server 2016 and later support the function when the database compatibility level is at least 130.
What STRING_SPLIT Does
STRING_SPLIT is a table-valued function: rather than returning one scalar value, it returns rows. Its first argument is the string to divide, and its second argument is a single-character separator. For a comma-delimited string, each item between commas becomes a row in the result.
The function is useful when a query needs to turn a list stored in one field into separate values—for example, to filter against a list of IDs supplied by an application, or to expand a simple comma-separated tags column. It does not create a permanent table or change the source data. The rows exist in the query result unless you explicitly insert them elsewhere.
The returned column is named value. Its data type is nvarchar if either input argument is Unicode; otherwise, it is varchar. The value column’s declared length is based on the length of the input string, so a short token can still have a relatively wide type. SQL Server can use the rows in joins, filters, and other query operations just like rows from other table sources.
Basic Syntax and Example
The basic syntax is STRING_SPLIT(string, separator). The separator must be one character, such as a comma, pipe, or semicolon. A basic query looks like this:
SELECT value
FROM STRING_SPLIT('red,green,blue', ',');
This returns three rows containing red, green, and blue. Do not assume that the rows will come back in the same order as the original text: without an explicit ordering column, SQL Server does not guarantee result order.
You can give the result a more descriptive column name with an alias:
SELECT s.value AS color
FROM STRING_SPLIT('red,green,blue', ',') AS s;
That alias is especially helpful in larger queries, where several sources may have columns with generic names such as value. If the input is a column rather than a literal, pass the column in the first argument and use the same syntax.
SQL Server Versions and Compatibility Level
STRING_SPLIT was introduced in SQL Server 2016. On SQL Server, the database must have a compatibility level of 130 or higher for the function to be available. A server can run a newer SQL Server release while an individual database remains at an older compatibility level, so the server version alone may not explain why a query reports that the function is invalid.
Check a database’s compatibility level with this query:
SELECT compatibility_level
FROM sys.databases
WHERE name = DB_NAME();
If the level is below 130, a database administrator can assess whether changing it is appropriate. Compatibility level can affect other query behaviors, so changing it should not be treated as a quick fix without checking the application and database requirements. Azure SQL services and other SQL platforms can have different release and compatibility details; consult the documentation for the specific service.
SQL Server 2022 added an optional third argument that enables an ordinal column. That argument is also available in supported Azure SQL offerings. On earlier SQL Server versions, use a different splitting method if you need the original positions attached to the output.
Ordering the Split Results
A frequent misconception is that STRING_SPLIT always returns tokens in input order. It does not promise that. Even if a query appears to return values in the expected sequence during one run, applications must not rely on that behavior unless the query has an explicit ORDER BY.
With SQL Server 2022 or a supported Azure SQL service, pass the constant 1 as the third argument to request an ordinal column. The ordinal records each token’s position, starting at 1. Then sort by it:
SELECT ordinal, value
FROM STRING_SPLIT('red,green,blue', ',', 1)
ORDER BY ordinal;
The result has an ordinal column of type bigint. The third argument must be a constant value of 0 or 1, not a column or variable. Setting it to 1 asks for the column; omitting it, or using 0, does not. The ordinal makes ordering explicit, but the query still needs ORDER BY ordinal to guarantee the displayed order.
Empty Tokens, NULL Values, and Whitespace
Consecutive separators can produce empty tokens. For example, splitting 'a,,b' on a comma produces values for a, an empty string, and b. A separator at the start or end can likewise leave an empty value at that position. An empty string as the input, by contrast, produces an empty table. A NULL input also returns an empty table rather than a row containing NULL.
These differences matter when counting tokens, validating user input, or joining the output to another table. To remove empty values, filter them explicitly:
SELECT value
FROM STRING_SPLIT('a,,b', ',')
WHERE value <> '';
Splitting does not automatically trim spaces. If the input is 'red, green', the second value includes a leading space. You can trim the results with LTRIM and RTRIM, which are available on older SQL Server versions too:
SELECT LTRIM(RTRIM(value)) AS item
FROM STRING_SPLIT('red, green', ',');
SQL Server 2017 and later also support TRIM. Decide whether to discard empty values before or after trimming: a token containing only spaces is not the empty string until it has been trimmed.
Using STRING_SPLIT with Table Data
To split a delimited value stored in each row of a table, use CROSS APPLY. It evaluates the function for each source row and combines the returned tokens with that row’s other columns:
SELECT p.ProductID, s.value AS tag
FROM dbo.Products AS p
CROSS APPLY STRING_SPLIT(p.Tags, ',') AS s;
If a product has Tags set to 'sale,summer', the query returns two rows for that product, one for each tag. A NULL tag string returns no split rows, so CROSS APPLY excludes that product from the result. Use OUTER APPLY instead if you need to preserve source rows that have no returned tokens; the split columns will be NULL for those rows.
A common use is matching a simple input list against a table:
SELECT c.CustomerID, c.CustomerName
FROM dbo.Customers AS c
WHERE c.CustomerID IN
(
SELECT TRY_CONVERT(int, value)
FROM STRING_SPLIT('12,18,25', ',')
);
TRY_CONVERT returns NULL for a token that cannot be converted to an integer, avoiding a conversion error. For production queries, validate the input and consider how invalid, empty, or duplicate tokens should be handled. If the list is large or used repeatedly, passing it as a table-valued parameter or storing the values in a properly typed table can be clearer and more efficient.
Important Limitations
The separator is a single character, not a string of multiple characters. You cannot directly split on a delimiter such as || with one call. Also, STRING_SPLIT is not a full CSV parser. A comma inside a quoted CSV field is data, not a delimiter, but this function does not interpret CSV quoting rules. For example, a value such as "New York, NY" needs quote-aware parsing to remain one field.
The function also does not validate the meaning or type of the tokens. It simply returns text values for the pieces it finds. Convert values deliberately, handle conversion failures, and apply any required validation in the query or application. The character with code zero, 0x0000, cannot be included as the separator in Windows collations.
Finally, a delimited string in a database column can make searching and updating harder. If values have independent meaning, need their own constraints, or are frequently joined and filtered, a related table with one value per row is usually a stronger data model. Splitting is convenient for importing or handling simple input lists, but it is not a substitute for choosing an appropriate storage design.
Alternatives and When to Choose Them
For ordinary one-character-delimited strings, STRING_SPLIT is usually the simplest built-in option. Consider another approach when the delimiter is multiple characters, when the format includes quoting or escaping, when the SQL Server version is too old, or when token positions are needed on a version without the ordinal argument.
- Table-valued parameters: When an application sends a list to a stored procedure, a table-valued parameter can pass typed rows directly instead of packing them into a string and splitting it.
- XML or JSON parsing: These formats can represent structured values, but they require properly formed input and suitable parsing functions. Choose them when the data is genuinely structured, not merely to avoid a simple split.
- A numbers or tally table: A carefully written query using character positions can support custom delimiters or older SQL Server versions. It is more complex and should be tested for edge cases and performance.
- Application-side parsing: A programming language’s CSV parser is a better fit for quoted fields, escaped delimiters, and other CSV rules. Keep parsing consistent with the format’s specification.
Pick based on the input format and how the values are used, not only on the shortest query. For a small, simple comma-separated list, STRING_SPLIT is direct. For recurring relational operations, typed rows are often easier to validate and maintain.
Frequently Asked Questions
Does SQL Server have a SPLIT function?
SQL Server’s built-in function is named STRING_SPLIT, not simply SPLIT. It returns a table of tokens separated by a single character.
Why does STRING_SPLIT say it is invalid?
Check that SQL Server is version 2016 or later and that the database compatibility level is at least 130. Also confirm that the query is running against the database and platform you expect.
Does STRING_SPLIT preserve the original order?
Not by default. On SQL Server 2022 and supported Azure SQL services, enable the ordinal column with the third argument set to 1 and add ORDER BY ordinal.
How do I remove blank values from STRING_SPLIT?
Filter out empty strings with WHERE value <> ''. If tokens may contain spaces, trim them first or use a trimmed expression in the filter.
Can STRING_SPLIT use a multi-character delimiter?
No. Its separator is one character. For a multi-character delimiter, use a custom parsing method or transform the input carefully before splitting; use a format-aware parser if the data includes quoting or escaping.
Related
- Balance of Payments Notes: Accounts, Examples and Meaning
- Java split(): How It Handles Delimiters and Limits
- Java String split(): Syntax, Regex, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter Cells Containing Specific TextTo filter Excel rows for cells containing particular text, select a cell in your data, choose Data >…
- Excel TODAY Formula for Days Between DatesTo calculate how many days have passed from a date in cell A2 to today, enter =TODAY()-A2 in…
- How to Find Unique Names in an Excel Column EasilyIn Microsoft 365, you can create a live list of distinct names with the UNIQUE function. With names…
- Excel Filter Out Errors: Quick Methods That WorkTo filter out error rows in Excel for Microsoft 365 on Windows, select your data, choose Data >…
- Excel Sum Lookup: Sum Values That Match a CriterionTo add every value that matches a lookup criterion in Excel, use SUMIF. With product names in A2:A10,…
- How to Put a SUM Formula in an Excel Column of NumbersTo total numbers in an Excel column, select the cell where the answer should appear, type a SUM…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.