The `STRING_SPLIT` string function is a table-valued function that splits a string into rows of substrings, based on a specified separator character.
`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'.
STRING_SPLIT ( string , separator [ , enable_ordinal ] )
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
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.
Here are sample uses of the `STRING_SPLIT` string function
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 |
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 |
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 |
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 |
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 |
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 |