How STRING_SPLIT works in SQL? Best STRING_SPLIT examples

When working with SQL, we often encounter a scenario where a single column contains multiple values stored as a delimited string. Handling this efficiently is crucial, and that’s where STRING_SPLIT comes into play. Let’s dive deep into how STRING_SPLIT works in SQL and explore some of the best examples.

What is STRING_SPLIT?

STRING_SPLIT is a table-valued function in SQL Server that allows us to break a string into multiple rows based on a specified delimiter. This function is especially useful when dealing with comma-separated values (CSV) or other delimiters.

Basic Syntax of STRING_SPLIT

The basic syntax of STRING_SPLIT looks like this:

SELECT value
FROM STRING_SPLIT(string, separator);

Where:

• string – The input string to be split.
• separator – The delimiter that defines where the string should be split.

Simple Example of STRING_SPLIT

Let’s start with a basic example where we split a comma-separated string:

SELECT value
FROM STRING_SPLIT('apple,banana,orange', ',');

Output:

Using STRING_SPLIT with a Table Column

In real-world use cases, we often need to split values stored inside a table column. Let’s assume we have a table called Users with a column Tags storing multiple tags as a comma-separated string.

CREATE TABLE Users (
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    Tags VARCHAR(255)
);

INSERT INTO Users (Id, Name, Tags) VALUES
(1, 'Alice', 'SQL,Database,Performance'),
(2, 'Bob', 'BI,SQL,ETL');

Now, let’s split the tags:

SELECT Name, value AS Tag
FROM Users
CROSS APPLY STRING_SPLIT(Tags, ',');

Output:

Limitations of STRING_SPLIT

While STRING_SPLIT is incredibly useful, it has some limitations:

• It does not preserve the original order of the elements unless SQL Server 2017+ with compatibility level 140 or higher is used, which introduces an ordinal column.
• It only works with a single-character delimiter, meaning you can’t split using substrings like ", ".
• It cannot be directly used in older versions of SQL Server (before 2016).

Handling STRING_SPLIT with Ordinality

From SQL Server 2017 (with compatibility level 140 or higher), STRING_SPLIT includes an ordinal column that maintains the original sequence of values.

SELECT value, ordinal
FROM STRING_SPLIT('apple,banana,orange', ',') WITH (ORDINALITY);

Output:

Alternative Approaches to STRING_SPLIT

If STRING_SPLIT does not meet your specific needs, consider these alternatives:

1. Using XML parsing with STRING_AGG for complex splitting.
2. Creating a table-valued function using a recursive common table expression (CTE).
3. Using a numbers table to split strings efficiently.

Final Thoughts

STRING_SPLIT is a powerful tool when dealing with delimited strings in SQL. It provides a simple and efficient way to break down data into meaningful rows. However, understanding its limitations and knowing alternative approaches can be beneficial when working with more complex scenarios.