How STRING_SPLIT works in SQL? Best STRING_SPLIT examples

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:

value
apple
banana
orange

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:

Name Tag
Alice SQL
Alice Database
Alice Performance
Bob BI
Bob SQL
Bob ETL

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:

value ordinal
apple 1
banana 2
orange 3

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.

 

Other interesting article:

How INITCAP works in SQL? Best INITCAP examples