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.