STRING_SPLIT String Function¶
The STRING_SPLIT string function is a table-valued function that splits a string into rows of substrings, based on a specified separator character.
Compatibility level 130¶
STRING_SPLIT requires the compatibility level to be at least 130. When the level is less than 130, the Database Engine is unable to find the STRING_SPLIT function.
SELECT compatibility_level
FROM sys.databases WHERE name = 'SQLServerHelper';
| compatibility_level |
|---|
| 110 |
SELECT * FROM STRING_SPLIT('A,B,C', ',');
Query 1 ERROR at Line 22: : Msg: 208, Line 1, State: 1, Level: 16
Invalid object name 'STRING_SPLIT'.
Syntax¶
STRING_SPLIT ( string , separator [ , enable_ordinal ] )
Arguments¶
string
An expression of any character type (for example, nvarchar, varchar, nchar, or char).
separator
A single character expression of any character type (for example, nvarchar(1), varchar(1), nchar(1), or char(1)) that is used as separator for concatenated substrings.
enable_ordinal
An int or bit expression that serves as a flag to enable or disable the ordinal output column. A value of 1 enables the ordinal column. If enable_ordinal is omitted, NULL, or has a value of 0, the ordinal column is disabled.
Note: This option is only available to Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (serverless SQL pool only), and SQL Server 2022 (16.x) and later versions
Return Types¶
If the ordinal output column isn't enabled, STRING_SPLIT returns a single-column table whose rows are the substrings. The name of the column is value. It returns nvarchar if any of the input arguments are either nvarchar or nchar. Otherwise, it returns varchar. The length of the return type is the same as the length of the string argument.
If the enable_ordinal argument is passed a value of 1, a second column named ordinal is returned that consists of the 1-based index values of each substring's position in the input string. The return type is bigint.
Sample Uses of the STRING_SPLIT String Function¶
Here are sample uses of the STRING_SPLIT string function
Usage #1 : Transpose a Table¶
WITH NBAChampions AS (
SELECT *
FROM (VALUES ('Boston Celtics', '2008,2024'),
('Cleveland Cavaliers', '2016'),
('Dallas Mavericks', '2011'),
('Denver Nuggets', '2023'),
('Detroit Pistons', '2004'),
('Golden State Warriors', '2015,2017,2018,2022'),
('Los Angeles Lakers', '2000,2001,2002,2009,2010,2020'),
('Miami Heat', '2006,2012,2013'),
('Milwaukee Bucks', '2021'),
('San Antonio Spurs', '2003,2005,2007,2014'),
('Toronto Raptors', '2019'))
AS [NBATeams] ( [TeamName], [YearsWon])
)
SELECT [value] AS [Year], [TeamName]
FROM NBAChampions CROSS APPLY STRING_SPLIT([YearsWon], ',')
ORDER BY [value];
| Year | TeamName |
|---|---|
| 2000 | Los Angeles Lakers |
| 2001 | Los Angeles Lakers |
| 2002 | Los Angeles Lakers |
| 2003 | San Antonio Spurs |
| 2004 | Detroit Pistons |
| 2005 | San Antonio Spurs |
| 2006 | Miami Heat |
| 2007 | San Antonio Spurs |
| 2008 | Boston Celtics |
| 2009 | Los Angeles Lakers |
| 2010 | Los Angeles Lakers |
| 2011 | Dallas Mavericks |
| 2012 | Miami Heat |
| 2013 | Miami Heat |
| 2014 | San Antonio Spurs |
| 2015 | Golden State Warriors |
| 2016 | Cleveland Cavaliers |
| 2017 | Golden State Warriors |
| 2018 | Golden State Warriors |
| 2019 | Toronto Raptors |
| 2020 | Los Angeles Lakers |
| 2021 | Milwaukee Bucks |
| 2022 | Golden State Warriors |
| 2023 | Denver Nuggets |
| 2024 | Boston Celtics |
Usage #2 : Sort a Comma-Delimited List of IPv4 Addresses¶
DECLARE @Input VARCHAR(1000)
DECLARE @Output VARCHAR(1000)
SET @Input = '10.0.0.1,192.120.40.243,207.46.199.60,255.255.0.0,255.255.255.255,64.233.188.15,98.123.251.21';
SELECT TOP 100 @Output = ISNULL(@Output + ',' + value, value)
FROM STRING_SPLIT(@Input, ',')
ORDER BY CAST(PARSENAME([value], 4) AS INT),
CAST(PARSENAME([value], 3) AS INT),
CAST(PARSENAME([value], 2) AS INT),
CAST(PARSENAME([value], 1) AS INT);
SELECT @Output AS [Output]
| Output |
|---|
| 10.0.0.1,64.233.188.15,98.123.251.21,192.120.40.243,207.46.199.60,255.255.0.0,255.255.255.255 |
Take note of the TOP 100 clause in the query. Theoretically, that clause is not needed to generate the desired output but somehow it seems that there is bug with the STRING_SPLIT string function when the value column is assigned to a variable (in this case, the @Output variable) and the query includes an ORDER BY clause wherein the expression used is not just the value column by itself but an expression that uses operators.
Using the same query above but not include the TOP 100 clause:
DECLARE @Input VARCHAR(1000)
DECLARE @Output VARCHAR(1000)
SET @Input = '10.0.0.1,192.120.40.243,207.46.199.60,255.255.0.0,255.255.255.255,64.233.188.15,98.123.251.21';
SELECT @Output = ISNULL(@Output + ',' + value, value)
FROM STRING_SPLIT(@Input, ',')
ORDER BY CAST(PARSENAME([value], 4) AS INT),
CAST(PARSENAME([value], 3) AS INT),
CAST(PARSENAME([value], 2) AS INT),
CAST(PARSENAME([value], 1) AS INT);
SELECT @Output AS [Output];
The output of the query is as follows:
| Output |
|---|
| 255.255.255.255 |
Usage #3 : Sort a Comma-Delimited String¶
DECLARE @Input VARCHAR(1000)
DECLARE @Output VARCHAR(1000)
SET @Input = 'one,two,three,four,five,six,seven,eight,nine,ten'
SELECT @Output = ISNULL(@Output + ',' + value, value)
FROM STRING_SPLIT(@Input, ',')
ORDER BY value;
SELECT @Output AS [Output];
| Output |
|---|
| eight,five,four,nine,one,seven,six,ten,three,two |
Usage #4 : Create a Integer Table¶
WITH Digits AS (
SELECT CAST(value AS TINYINT) AS value
FROM STRING_SPLIT('0,1,2,3,4,5,6,7,8,9', ',')
)
SELECT (tens.value * 10) + ones.value AS Number
FROM Digits tens CROSS JOIN Digits ones
ORDER BY Number;
| Number |
|---|
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| ... |
| 95 |
| 96 |
| 97 |
| 98 |
| 99 |
Usage #5 : Parse a Query String¶
DECLARE @QueryString VARCHAR(100)
SET @QueryString = 'street=1600+Pennsylvania+Avenue+NW&city=Washington&state=DC&zip=20500'
SELECT LEFT(value, CHARINDEX('=', value) - 1) AS [Key],
RIGHT(value, CHARINDEX('=', REVERSE(value)) - 1) AS [Value]
FROM STRING_SPLIT(@QueryString, '&');
| Key | Value |
|---|---|
| street | 1600+Pennsylvania+Avenue+NW |
| city | Washington |
| state | DC |
| zip | 20500 |
Usage #6 : Create a Multiplication Table¶
WITH Digits AS (
SELECT CAST(value AS TINYINT) AS value
FROM STRING_SPLIT('1,2,3,4,5', ',')
)
SELECT CONCAT(d1.value, ' * ', d2.value, ' = ', d1.value * d2.value) AS MultiplicationTable
FROM Digits d1 CROSS JOIN Digits d2;
| MultiplicationTable |
|---|
| 1 * 1 = 1 |
| 1 * 2 = 2 |
| 1 * 3 = 3 |
| 1 * 4 = 4 |
| 1 * 5 = 5 |
| 2 * 1 = 2 |
| 2 * 2 = 4 |
| 2 * 3 = 6 |
| 2 * 4 = 8 |
| 2 * 5 = 10 |
| 3 * 1 = 3 |
| 3 * 2 = 6 |
| 3 * 3 = 9 |
| 3 * 4 = 12 |
| 3 * 5 = 15 |
| 4 * 1 = 4 |
| 4 * 2 = 8 |
| 4 * 3 = 12 |
| 4 * 4 = 16 |
| 4 * 5 = 20 |
| 5 * 1 = 5 |
| 5 * 2 = 10 |
| 5 * 3 = 15 |
| 5 * 4 = 20 |
| 5 * 5 = 25 |