T-SQL STRING_SPLIT: Syntax, Order, and Examples

How-to basics · Updated November 22, 2026

In T-SQL, use SQL Server’s built-in STRING_SPLIT function to turn a delimited string into a table of rows. It is available in SQL Server 2016 and later when the database compatibility level is at least 130; in SQL Server 2022 and supported newer services, an optional argument can also return each item’s original position.

What STRING_SPLIT does

STRING_SPLIT is a table-valued function: rather than returning one text value, it returns a result set with one row for each substring separated by the delimiter. That makes it useful when data arrives as a simple list, such as a comma-separated set of product codes, and a query needs to filter or join against the individual items.

For example, splitting N'apple,pear,plum' on a comma produces three rows containing apple, pear, and plum. The result is a relational rowset, so it can be used in a FROM clause, joined to another table, filtered, or inserted into a temporary or permanent table.

The function does not turn arbitrary text formats into structured data. Its delimiter is a single character, and it does not interpret quoting or escaping rules. A comma-separated string with quoted commas is not reliably parsed as CSV just because the delimiter is a comma.

Syntax and version requirements

The basic syntax is STRING_SPLIT(string, separator). The string is the text to split, and the separator is the character that marks each boundary. The function returns a column named value. The expression types determine whether that output is varchar or nvarchar, and its declared length is based on the input string.

On SQL Server 2016 through SQL Server 2019, use the two-argument form. SQL Server 2022 introduced an optional third argument, enable_ordinal, which can be set to the constant 1 to include an ordinal column. The ordinal is a bigint indicating the item’s one-based position in the input. Availability of that argument depends on the SQL Server version or supported Azure service; it is not available in every older installation.

For SQL Server 2016 and later, check the database compatibility level if the function is reported as unknown. You can inspect it with SELECT name, compatibility_level FROM sys.databases;. A database running at a level below 130 may need its compatibility level raised, but changing that setting can affect other query behavior, so assess the database as a whole rather than changing it solely to enable one function.

Basic use in a query

Here is a simple query that splits a literal list into rows:

SELECT value
FROM STRING_SPLIT(N'apple,pear,plum', N',');

The N prefix marks the string literals as Unicode. The output contains one value per item, but the function does not promise that rows will be returned in the same order as the text. If you only need to test membership or aggregate values, order may not matter. If order matters, use the ordinal option where available, as described below.

You can also split a variable or a column. For example, if a table has a column named TagList containing comma-delimited tags, a query can apply the function to each row with CROSS APPLY:

SELECT p.ProductID, s.value AS Tag
FROM dbo.Product AS p
CROSS APPLY STRING_SPLIT(p.TagList, N',') AS s;

CROSS APPLY evaluates the table-valued function for each product row and combines its returned items with that row. Products whose list produces no rows will not appear in this result. Use OUTER APPLY instead if the query should retain the product even when the function returns no items; columns from the function will then be NULL for that product.

Preserving and sorting the original order

A common misconception is that the rows come back in input order because they look ordered in a quick test. SQL result sets have no guaranteed order unless the outer query includes an ORDER BY. This rule applies even when a function appears to return values in a consistent sequence on one server or plan.

On SQL Server 2022 and supported services, request ordinals with the third argument and explicitly sort by that column:

SELECT ordinal, value
FROM STRING_SPLIT(N'apple,pear,plum', N',', 1)
ORDER BY ordinal;

The third argument must be a constant value of 0 or 1; use 1 to enable the ordinal output. With ordinals enabled, the function returns both value and ordinal. The ordinal records where each substring appeared, but it still does not make the result ordered by itself: ORDER BY ordinal is what guarantees display order.

On earlier SQL Server versions, the two-argument function does not provide ordinals. If preserving the position is essential, consider splitting the input in application code, storing the items in a table with an explicit position column, or using a custom splitter designed to return positions. Avoid trying to reconstruct the original order from the values after the fact: duplicate items make that ambiguous.

Empty items, whitespace, and NULL

Repeated delimiters can create empty substrings. For instance, splitting N'apple,,plum' on a comma includes an empty value between the two commas. An empty token is different from a NULL value, and it may need to be removed or handled before the values are used. A trailing delimiter can likewise represent an empty final item, so test the exact input shapes your application allows.

Spaces around tokens are not automatically trimmed. If the list is N'apple, pear', the second value includes its leading space. You can trim values in the query and filter out empty results:

SELECT LTRIM(RTRIM(value)) AS item
FROM STRING_SPLIT(N'apple, pear,,plum', N',')
WHERE LTRIM(RTRIM(value)) <> N'';

LTRIM and RTRIM are used here for broad compatibility. The TRIM function is available in SQL Server 2017 and later. Trimming is a data decision, not merely a formatting step: if leading or trailing spaces are meaningful in the values, removing them changes the input. A NULL string expression returns an empty table, so it produces no split rows rather than a row whose value is NULL.

Converting tokens and joining to data

When a list contains identifiers, splitting is only one step. The resulting values are text, so convert them to the type expected by the table you want to query. For potentially messy input, TRY_CONVERT can discard values that are not valid integers instead of raising a conversion error:

DECLARE @ids nvarchar(100) = N'12,27,not-a-number,41';

SELECT p.ProductID, p.ProductName
FROM dbo.Product AS p
JOIN (
    SELECT TRY_CONVERT(int, value) AS ProductID
    FROM STRING_SPLIT(@ids, N',')
) AS list
    ON list.ProductID = p.ProductID
WHERE list.ProductID IS NOT NULL;

The derived table converts each token, and the filter excludes failed conversions, which become NULL. In production, consider trimming tokens before conversion if users may include spaces. Also decide how to handle duplicate identifiers: a join can return duplicate product rows when the input repeats an ID. Use a distinct list or an existence check if duplicates should not multiply results.

For a frequently used list of values, a table-valued parameter or a properly normalized child table is often a better interface than passing a delimited string. Those approaches provide typed rows directly and avoid repeatedly parsing text. STRING_SPLIT is convenient for simple inputs, but it does not replace a sound data model.

Delimiter limits and CSV misconceptions

The separator must be a single character. A multi-character separator such as N'||' is not supported as one delimiter; split in stages only if that is genuinely safe for the data, or use a parser that accepts the required format. The char(0) character is also unsupported in Windows collations.

Most importantly, a delimiter split is not a full CSV parser. In CSV, a field can contain a comma inside quotation marks, and quotation marks may be escaped according to format rules. A simple split on commas cannot distinguish a comma inside a quoted field from a separator between fields. For files, use a CSV-capable import process; for application input, parse the format with a suitable library before sending structured values to SQL Server.

Think about how the delimiter is chosen, too. If tokens themselves can contain that character, the input format needs an escaping convention or a different representation. Without such a rule, there is no reliable way for a simple splitter to know which delimiter characters are boundaries.

Choosing STRING_SPLIT or another method

For a short, straightforward, single-character-delimited list on a supported SQL Server version, STRING_SPLIT is usually the clearest built-in option. Its suitability depends on the shape of the data and the requirements of the query, not just on whether the function is available.

  • Use STRING_SPLIT for uncomplicated lists when each item is separated by one character and you can handle ordering, trimming, and conversion explicitly.
  • Use a table-valued parameter when an application regularly passes a collection of typed values to a stored procedure.
  • Use a normalized table when the items are durable parts of the data model that need their own keys, constraints, or relationships.
  • Use a format-aware parser for CSV or another format with quoting, escaping, or multi-character separators.
  • Consider a custom splitter on older SQL Server versions when position information or specialized parsing is required and changing the input interface is not practical.

Older techniques include XML-based splitting and user-defined functions. They can be useful for legacy systems, but they add complexity and have their own input and performance considerations. There is no universally fastest method for every workload: test representative data and query plans if parsing is on a frequent or performance-sensitive path.

Frequently Asked Questions

Is STRING_SPLIT available in SQL Server 2014?

No. The built-in function was introduced in SQL Server 2016. Earlier versions need another approach, such as application-side parsing, a table-valued parameter, or a carefully chosen legacy splitter.

Does STRING_SPLIT return values in the original order?

Not reliably. Use the ordinal option where supported and add ORDER BY ordinal. Without ordinals, the original positions cannot be depended on.

Can STRING_SPLIT use a multi-character delimiter?

No. Its separator is a single character. Use a parser that supports the input format if the separator has multiple characters or tokens can contain the delimiter.

Why does STRING_SPLIT say it is an invalid object name?

Check that the server is SQL Server 2016 or later and that the database compatibility level is 130 or higher. Also verify that the query is running against the expected database.

How do I remove empty results?

Filter out empty values, and trim first if spaces should not count: for example, use WHERE LTRIM(RTRIM(value)) <> N''. Be sure that removing empty or whitespace-only tokens matches the meaning of your data.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.