Split in SQL Server: STRING_SPLIT and Practical Examples

How-to basics · Updated November 22, 2026

To split a delimited string into rows in SQL Server, use STRING_SPLIT, for example SELECT value FROM STRING_SPLIT('red,green,blue', ',');. It is available in SQL Server 2016 and later, subject to the database compatibility level, and SQL Server 2022 adds an option for returning each token’s position.

What “split” means in SQL Server

People asking how to split in SQL Server usually want to turn one string containing separators into multiple rows. For example, the value red,green,blue can become three rows containing red, green, and blue. SQL Server’s built-in function for this task is STRING_SPLIT.

Splitting a string into rows is not the same as dividing a table into partitions, separating a value into several fixed columns, or breaking a string at a multi-character phrase. Those tasks need different approaches. This article focuses on delimited text, such as comma-separated values stored in a column or passed as a parameter.

A delimited list can be handy when importing data or accepting a simple list from an application. It is usually not a good replacement for a relational design: when values need to be searched, updated, validated, or joined individually, storing one value per row is often easier to manage.

Basic STRING_SPLIT syntax

The function takes a string and a one-character separator. Its result is a table with a value column, so it can appear in a query’s FROM clause like a table-valued function:

SELECT value
FROM STRING_SPLIT('red,green,blue', ',');

The result contains one row for each separated token. The output column’s string type is based on the input string and separator: it is Unicode if either argument is Unicode, and otherwise it is a non-Unicode string type. The function does not remove spaces around tokens. For example, splitting red, green returns a second value with a leading space.

Use CROSS APPLY to split a column for every row in a table. For example, if dbo.Products has a Tags column containing comma-separated tags:

SELECT p.ProductId, s.value AS Tag
FROM dbo.Products AS p
CROSS APPLY STRING_SPLIT(p.Tags, ',') AS s;

Here, each product row is paired with the tokens produced from that product’s string. A product with three tags contributes three result rows. Because this is a cross apply, a source row that produces no tokens does not appear in the result. Use OUTER APPLY if you need to retain source rows even when the function returns no rows.

SQL Server versions and compatibility level

STRING_SPLIT was introduced in SQL Server 2016. On SQL Server, the database must also have a compatibility level of at least 130 for the function to be available. Compatibility level is a database setting, so the server version alone does not tell you whether a particular database can use it.

Check the current database’s setting with:

SELECT compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

If the query returns a level below 130, ask your database administrator whether raising it is appropriate. Changing compatibility level can affect query behavior beyond this one function; it should not be treated as a harmless switch on a production database. SQL Server 2022 introduced the optional ordinal argument described below. If that argument is rejected, check the SQL Server version as well as the database compatibility setting.

Token order and the ordinal option

A common trap is assuming that rows come back in the same order as the tokens in the original string. Without an explicit ordering column, SQL query results have no guaranteed order. Even if a quick test appears to return tokens in sequence, applications should not depend on that observed order.

SQL Server 2022 and later can return an ordinal position by passing a third argument of 1. The ordinal is a 1-based position for each token. Sort by it when order matters:

SELECT ordinal, value
FROM STRING_SPLIT('red,green,blue', ',', 1)
ORDER BY ordinal;

This returns positions 1, 2, and 3 alongside the corresponding values. The third argument must be a constant bit or integer value of 0 or 1; it is not a per-row switch that can vary for each input. On versions without ordinal support, STRING_SPLIT alone cannot reliably tell you the original position. If position is essential, use a method that explicitly generates positions or change the input format.

Empty values, spaces, and NULL

Consecutive separators can produce empty tokens. For instance, a string such as red,,blue includes an empty value between the two commas. Decide whether that represents meaningful data or should be discarded. An empty input string produces an empty table, while a NULL input produces an empty table as well; neither case produces a row containing NULL.

Whitespace is a separate issue: the function separates text but does not trim it. To remove leading and trailing spaces and omit tokens that become empty, use a query such as:

SELECT LTRIM(RTRIM(value)) AS value
FROM STRING_SPLIT('red, green,   ,blue', ',')
WHERE LTRIM(RTRIM(value)) <> '';

This example uses LTRIM and RTRIM for compatibility with older SQL Server versions. On newer versions, TRIM is another option. Be deliberate about filtering empty strings: a blank field may indicate malformed input, but it can also be an intentional value in a particular data format.

Convert tokens before using them

STRING_SPLIT returns text. If the separated values represent numbers, dates, or identifiers, convert and validate them before comparing or inserting them. A direct cast can fail the entire query if even one token is invalid. TRY_CONVERT returns NULL for a value it cannot convert, making it possible to identify bad entries:

SELECT value,
       TRY_CONVERT(int, LTRIM(RTRIM(value))) AS NumberValue
FROM STRING_SPLIT('12,27,not-a-number', ',');

The last token has a NULL NumberValue because it is not an integer. In a validation workflow, filter for conversion failures and report them rather than silently treating them as valid data. Also consider whether leading zeros matter: converting 0012 to an integer changes its representation to the number 12, which may be wrong for codes or account identifiers.

Joining split values to another table

A frequent practical use is matching tokens in a list against rows in a lookup table. For example, this query finds matching category names:

SELECT c.CategoryId, c.CategoryName
FROM STRING_SPLIT('Books,Games,Music', ',') AS s
JOIN dbo.Categories AS c
  ON c.CategoryName = LTRIM(RTRIM(s.value));

The comparison follows the data types and collation rules of the compared columns, so case sensitivity and accent sensitivity can depend on the database or column collation. If the list can contain duplicates, the join may also return a lookup row more than once. Use DISTINCT only if duplicate tokens should be treated as irrelevant; otherwise, preserve them and handle them according to the application’s rules.

For a numeric ID list, convert tokens safely before joining, and exclude failed conversions. Avoid assuming that every value supplied by a caller is well-formed. If this list comes from an untrusted source, validation is part of the data-handling requirement, not an optional cleanup.

Limitations and common mistakes

  • Expecting a multi-character separator: the separator argument is one character, not a phrase such as || or a line break sequence. For multi-character delimiters, use a different parsing approach or normalize the input first.
  • Assuming the output is ordered: add an ordinal-aware method and an ORDER BY when position matters.
  • Forgetting whitespace: splitting does not trim tokens, so clean them explicitly if spaces should not be part of a value.
  • Treating text as typed data: convert and validate values before arithmetic, date comparisons, or joins to typed columns.
  • Using a delimited column as a permanent data model: searching for one item inside a list is more cumbersome than joining to a related table with one item per row.

There is also a parsing limitation: a simple separator does not understand quoting or escaping. If a comma inside a quoted field is meant to be part of the value, a basic split will still treat it as a separator. That makes STRING_SPLIT unsuitable as a general CSV parser for files with quoted fields.

Alternatives and choosing the right approach

For a straightforward list with a single-character delimiter, STRING_SPLIT is usually the clearest built-in choice. Other approaches make sense when the input or application has different requirements:

  • JSON arrays: OPENJSON can read a properly formatted JSON array, which supports structured values and avoids treating every comma as a delimiter. The input must be valid JSON, and values still need appropriate validation and conversion.
  • Table-valued parameters: when an application sends a list to a stored procedure, a table-valued parameter can pass rows directly instead of packing them into a string. This is often a better interface when the client can send structured data.
  • A normalized child table: for values that are routinely queried or maintained, store each value as a row related to its parent. This supports constraints, indexes, and ordinary joins more naturally.
  • Custom parsing: older SQL Server versions or multi-character delimiters may require a numbers table or another explicit parser. Choose a method that handles the actual input rules, including empty values and escaping.

For a one-off import or a small parameter list, splitting a string can be perfectly reasonable. For a large, frequently queried dataset, compare the complete query plan and workload rather than assuming that splitting text will perform like joining indexed relational data.

Frequently Asked Questions

Does STRING_SPLIT preserve the original order?
No order is guaranteed unless the query orders its results. SQL Server 2022 and later can return an ordinal with the third argument set to 1; use ORDER BY ordinal to restore token order.

Can STRING_SPLIT use a delimiter such as two pipes?
No. Its separator is a single character. For a multi-character delimiter, use another parsing strategy or transform the input carefully before splitting.

Why does SQL Server say STRING_SPLIT is invalid?
Check that the server is SQL Server 2016 or later and that the database compatibility level is at least 130. If you used the ordinal argument, also confirm that the SQL Server version supports it.

Does STRING_SPLIT remove spaces or empty values?
No. It does not trim token text, and consecutive delimiters can produce empty tokens. Apply trimming and filtering explicitly if that matches your data rules.

Can I split a string into columns instead of rows?
STRING_SPLIT returns rows, not a fixed set of columns. If you need named columns, use a structured format such as JSON, or write a query that maps known positions to columns using an appropriate parsing method.

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.