How to Use the STRING_SPLIT Function in SQL Server

The STRING_SPLIT function is a game-changer for working with delimited strings in SQL Server. First introduced in SQL Server 2016, this function allows you to split a delimited string into rows, making it much easier to manage and query complex datasets. Let’s walk through how it works and some practical real-world examples.

What is STRING_SPLIT?

The STRING_SPLIT function breaks apart a string into rows based on a specified delimiter. This is incredibly handy when you’re working with data stored in a single column as a delimited string.

For example, if you have a string like “A B C D” separated by spaces, this function will split the string into rows:

Row 1: A
Row 2: B
Row 3: C
Row 4: D

The function is officially supported from SQL Server 2016 and requires a database compatibility level of 130 or above.

Checking Database Compatibility

One common issue with STRING_SPLIT is seeing an error even when your SQL Server is up-to-date. For example, running the function on a SQL Server 2022 instance might return an error:

Invalid object name 'STRING_SPLIT'

This happens because the database being used isn’t set to the required compatibility level. To resolve this:

Run the query below to check your database compatibility level:
```
SELECT name, compatibility_level  
FROM master.sys.databases;
```
Compatibility levels:
- 120: SQL Server 2014 or earlier
- 130: SQL Server 2016
If your database isn’t at 130 or higher, update it using the following command:
```
ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 160;
```
Replace YourDatabaseName with the name of your database. For SQL Server 2022, set the value to 160.

Once updated, STRING_SPLIT will be ready to use.

Basic Usage of STRING_SPLIT

Here’s a simple example of using STRING_SPLIT to separate data based on spaces:

SELECT value  
FROM STRING_SPLIT('A BB CC DD E', ' ');

This query outputs each element of the string as separate rows. The first argument is the string, and the second is the delimiter (a space in this case).

Adding the Ordinal Column

An optional argument, ordinal, can be used to capture the position of the split values. For instance:

SELECT value, ordinal  
FROM STRING_SPLIT('A,BB,CC,DD,E', ',') WITH (ORDINALITY);

This outputs the split string along with their order. The ordinal column can help when dealing with scenarios requiring the sequence of elements, like ordering or filtering based on positions.

Using STRING_SPLIT with Tables

Imagine you have a table named Products with a column Tags containing comma-separated values. Here’s an example record:
ProductName: Bike | Tags: Road,Mountain,Biking

Scenario 1: Split Delimited Tags into Rows

To break the Tags column into separate rows while preserving the ProductName:

SELECT ProductID, ProductName, value AS Tag  
FROM Products  
CROSS APPLY STRING_SPLIT(Tags, ',');

The CROSS APPLY operator is used because STRING_SPLIT is a table-valued function. This will output a new row for each tag while keeping the product details intact.

Scenario 2: Search for Specific Tags

Say you want to find all products with a tag like "Clothing". A simple query for this would be:

SELECT *  
FROM Products  
WHERE 'Clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ','));

If you want products with multiple tags like "Clothing" or "Road," you can use the EXISTS clause:

SELECT *  
FROM Products p  
WHERE EXISTS (  
    SELECT 1  
    FROM STRING_SPLIT(p.Tags, ',')  
    WHERE value IN ('Clothing', 'Road')  
);

This method ensures precise matching of tags, unlike a LIKE clause which might match unintended values (e.g., "Motorbike" when searching for "Bike").

Dynamic Filtering with STRING_SPLIT

Often, web applications need to filter based on multiple values. For example, selecting products using dynamic input parameters. If you receive a comma-separated string of product IDs like “1,2”, you can:

DECLARE @ProductIDs NVARCHAR(MAX) = '1,2';  

SELECT *  
FROM Products  
WHERE ProductID IN (  
    SELECT value  
    FROM STRING_SPLIT(@ProductIDs, ',')  
);

This approach splits the input string and matches the product IDs dynamically.

Working with the Ordinal Column

Using the ordinal parameter, you can perform advanced operations like sorting based on the split order. For example:

SELECT value, ordinal  
FROM STRING_SPLIT('A,BB,CC,DD,E', ',') WITH (ORDINALITY)  
ORDER BY ordinal DESC;

You can also filter rows based on the position. For instance, selecting only even-numbered rows:

SELECT value  
FROM (  
    SELECT value, ordinal  
    FROM STRING_SPLIT('A,B,C,D,E,F', ',') WITH (ORDINALITY)  
) AS SplitData  
WHERE ordinal % 2 = 0;

Wrapping Up

The STRING_SPLIT function is a powerful tool for handling delimited data in SQL Server. Whether you’re breaking tags into rows, filtering products dynamically, or working with ordered data, it simplifies complex tasks with ease. Just remember to ensure your database compatibility level is set to at least 130 for smooth execution.

For a deeper dive, explore the official documentation here. Try the examples above and see how STRING_SPLIT can make your SQL queries more efficient.