STRING_SPLIT

STRING_SPLIT#

Splits a string into rows of substrings at a single-character separator. Use it in the FROM clause to turn a delimited list into a table, or with CROSS APPLY to split a column of each row.

Syntax#

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Arguments#

string

The string to split: an expression of type char, nchar, varchar, nvarchar or sysname - a literal, a variable, a column, or any expression over them.

separator

A single character that separates the substrings, typed char(1), nchar(1), varchar(1) or nvarchar(1). A separator whose type is longer than one character is refused, even when its value is one character long.

enable_ordinal

Optional. A constant 1 adds an ordinal column that numbers the substrings; 0 or an omitted argument returns the value column only. The argument must be a constant: a variable or an expression is refused.

Return value#

Column name

Data type

Description

value

the type of string

One substring per row: nvarchar when string or separator is Unicode, otherwise varchar, of the same length as string; sysname gives a length of 128 and an untyped NULL a length of 1. The column is as nullable as string.

ordinal

bigint NOT NULL

The one-based position of the substring in the input. Present only when enable_ordinal is 1.

The rows come back in no particular order. Use ORDER BY ordinal when the input order matters.

Remarks#

A NULL string yields no rows. Under CROSS APPLY the driving row disappears; under OUTER APPLY it is kept, with NULL in the function’s columns. A NULL separator is an error (214), whether it is written as NULL or arrives through a variable or a column.

An empty string yields one empty substring, and adjacent separators, or a separator at either end, yield empty substrings: STRING_SPLIT(',a,,b,', ',') returns five rows. Trailing blanks of a char input are part of the last substring.

A column argument requires APPLY. CROSS APPLY STRING_SPLIT(t.tags, ',') splits each row’s value. Referencing another table’s column from a plain join, or from a comma-separated FROM list, is refused with error 4104, as SQL Server refuses it.

Where the split is evaluated#

  • In a database backed by SQL Server, the source runs the function when the dialect version of its connection has it, and otherwise Querona splits the strings itself. When the source cannot run it under APPLY with an argument from another table, the default federator renders the APPLY when its dialect version has the function; when it does not, or external federation is off, Querona splits the strings once per driving row. It does so only when the call is the whole right side of APPLY: a query on the right side that reads the other table and calls STRING_SPLIT - a derived table such as CROSS APPLY (SELECT TOP (1) value FROM STRING_SPLIT(c.ContactName, ' ') ORDER BY value), or an inline function whose body calls it - is refused with a message that names the function. Set the dialect version of the connection to the version of the server to have the server run it.

  • Anywhere else, the rows come from wherever the query runs: a SQL Server source or the default federator renders STRING_SPLIT when the query reaches one whose dialect version has it, and otherwise Querona splits the strings itself - once per driving row when the argument comes from another table under APPLY.

Which dialect version of a SQL Server connection has the function:

Dialect version

Without enable_ordinal

With enable_ordinal

SQL Server (no version), SQL Server 2014

No

No

SQL Server 2016, 2017, 2019

Yes

No

SQL Server 2022, 2025

Yes

Yes

Azure SQL Data Warehouse

Yes

No

The dialect version is part of the connection settings. Set it to the version of the server so that the server runs every call it can. The generic SQL Server version does not assume the server has STRING_SPLIT, because a SQL Server 2014 or older server does not.

Note

SQL Server finds STRING_SPLIT only in a database whose compatibility level is 130 or higher; at a lower level it answers error 208 “Invalid object name ‘STRING_SPLIT’”, whatever its version. Querona does not read the compatibility level of the source database, so a source database below level 130 needs a dialect version that does not have the function, such as SQL Server 2014.

Note

When Querona splits the string itself, it matches the separator character by character, exactly. SQL Server matches it under the collation of the string, so under a case-insensitive collation STRING_SPLIT('aXb', 'x') returns a and b on SQL Server and the whole string here. See Feature support for other differences.

Examples#

A delimited list as a table:

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

Keeping the input order:

SELECT value, ordinal FROM STRING_SPLIT('x,y,z', ',', 1) ORDER BY ordinal;

Splitting a column of every row, keeping rows whose column is NULL:

SELECT c.CustomerID, s.value AS word
FROM dbo.Customers c
OUTER APPLY STRING_SPLIT(c.Region, ' ') s
ORDER BY c.CustomerID, s.value;

See Also#