In SQL Server STRING_SPLIT() is a table-valued function that splits a string into rows of substrings, based on a specified separator character.
Each substring is returned on its own row, in a column called value.
Syntax
The syntax goes like this:
STRING_SPLIT ( string , separator [ , enable_ordinal ] )The enable_ordinal argument is a flag that enables or disables the ordinal output column. A value of 1 enables it, 0 or NULL disables it. This argument is supported in SQL Server 2022 (16.x) and later versions, as well as Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (serverless SQL pool only).
Example
Here’s a simple example to demonstrate:
SELECT * FROM STRING_SPLIT('Man eating shark', ' ');Result:
value ------ Man eating shark
Here I specified that the separator is a space, so it split the first argument wherever there’s a space.
The following example uses a comma as the separator:
SELECT * FROM STRING_SPLIT('Cat,Dog,Horse', ',');Result:
value ----- Cat Dog Horse
The separator we specify will depend on which character we want the string to be split by. The separator isn’t included in the output.
The ordinal Column
By default STRING_SPLIT() returns just one column – the value column. This is the column in which the substrings are returned. Depending on your SQL Server version, you may be able to include the ordinal column.
To include the ordinal column, add a third argument of 1, which enables the enable_ordinal flag:
SELECT * FROM STRING_SPLIT('Man eating shark', ' ', 1);Result:
value ordinal ---------------- -------------------- Man 1 eating 2 shark 3
The enable_ordinal argument must be either a bit or int data type with a value of 0 or 1. It can also be NULL, but this will produce the same result as 0 (i.e. the ordinal column is disabled).
Here’s what happens if I run the above code on a system that doesn’t support the ordinal column:
SELECT * FROM STRING_SPLIT('Man eating shark', ' ', 1);Result:
Msg 8144, Level 16, State 3, Line 1 Procedure or function STRING_SPLIT has too many arguments specified.
As mentioned, the enable_ordinal argument is supported in SQL Server 2022 (16.x) and later versions, as well as Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (serverless SQL pool only).
Sorting the Results
The order of the output rows isn’t guaranteed to be in the same order of the substrings in the input values. If the order is important, we can use an ORDER BY clause.
Here it is sorted by the value column:
SELECT *
FROM STRING_SPLIT('Man eating shark', ' ', 1)
ORDER BY value;Result:
value ordinal ---------------- -------------------- eating 2 Man 1 shark 3
And here it is sorted by the ordinal column in descending order:
SELECT *
FROM STRING_SPLIT('Man eating shark', ' ', 1)
ORDER BY ordinal DESC;Result:
value ordinal ---------------- -------------------- shark 3 eating 2 Man 1
A Database Example
Suppose we have the following data:
SELECT
ProductId,
ProductName
FROM Products;Result:
ProductId ProductName --------- ------------------------------- 1 Left handed screwdriver 2 Right handed screwdriver 3 Long Weight (blue) 4 Long Weight (green) 5 Sledge Hammer 6 Chainsaw 7 Straw Dog Box 8 Bottomless Coffee Mugs (4 Pack) 9 Tea Pot 10 Hammock
We can use STRING_SPLIT() against that data like this:
SELECT
ProductId,
ProductName
FROM Products
WHERE 'Weight' IN (SELECT * FROM STRING_SPLIT( ProductName, ' ' ));Result:
ProductId ProductName --------- ------------------- 3 Long Weight (blue) 4 Long Weight (green)
If we want to search for more than one substring, we can do this:
SELECT
ProductId,
ProductName
FROM Products
WHERE EXISTS (
SELECT * FROM STRING_SPLIT( ProductName, ' ' )
WHERE value IN ('Weight', 'Screwdriver')
);Result:
ProductId ProductName --------- ------------------------ 1 Left handed screwdriver 2 Right handed screwdriver 3 Long Weight (blue) 4 Long Weight (green)
More Information
See Microsoft’s documentation for STRING_SPLIT() for more examples and information about how this function works.