Hello and welcome back to another post!

Recently, I was reviewing some stored procedures used to generate reports. These procedures accepted several parameters — some nullable, others allowing multiple values.

In those cases, it’s common to receive a comma-separated list like this:

DECLARE @OrderIDs VARCHAR(100) = '1,2,3,4,5';

Whenever I see this pattern, I know someone probably suffered writing custom string-splitting logic. In many companies, developers created user-defined functions with complex code that often impacted performance.

Fortunately, starting with SQL Server 2016, Microsoft introduced a much better solution: STRING_SPLIT().

Enter STRING_SPLIT()

STRING_SPLIT() is a built-in table-valued function that splits a string based on a given separator.

Older versions of SQL Server (2014 and earlier) required workarounds using XML or custom functions. With STRING_SPLIT(), those hacks are no longer necessary.

Let’s look at a simple example.

DECLARE @Colors VARCHAR(100) = 'Red,Blue,Green,Yellow,Orange';

SELECT * FROM STRING_SPLIT(@Colors, ',');

Result:

value
------
Red
Blue
Green
Yellow
Orange

In this example, I declared a variable containing a comma-separated list. This simulates a parameter passed into a stored procedure.

The function accepts two arguments:

  1. The string to split
  2. The separator character

The separator must be of type CHAR(1), VARCHAR(1), NCHAR(1) or NVARCHAR(1). Commas, pipes, tabs — almost anything works.

What is the value column?

Since STRING_SPLIT() is a table-valued function, it returns a table.
That table contains a single column named value, which holds each split element.

Yes — that means you can do this:

SELECT value FROM STRING_SPLIT('Apple|Banana|Cherry', '|');

A More Realistic Example

While not exactly “real life”, this is a very common scenario.

First, let’s create a stored procedure that accepts a list of order IDs.

CREATE PROCEDURE Sales.GetOrdersByIDs
    @OrderIDs VARCHAR(MAX)
AS
BEGIN
    SELECT 
        o.OrderID,
        o.CustomerID,
        ol.StockItemID,
        ol.Description,
        ol.Quantity
    FROM 
        Sales.Orders o
        INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
        INNER JOIN STRING_SPLIT(@OrderIDs, ',') s ON o.OrderID = CAST(s.value AS INT)
    ORDER BY 
        o.OrderID;
END;

Notice how we join Sales.Orders and Sales.OrderLines with STRING_SPLIT(), using the value column as the join condition.

Since the function returns strings, I had to cast value to INT to match the order IDs.

Let’s run it.

EXEC Sales.GetOrdersByIDs @OrderIDs = '1,2,3,4,5';

Result:

OrderID  CustomerID  StockItemID  Description              Quantity
-------  ----------  -----------  -----------------------  --------
1        832         191          USB missile launcher     2
1        832         56           Ride on toy sedan car    1
2        803         100          DBA joke mug             3
2        803         227          Developer joke mug       1
3        910         191          USB missile launcher     5

It worked perfectly — we got only the orders specified in the comma-separated list.

For this example, I’m using SQL Server 2017 and the WideWorldImporters database.

If you don’t have it, you can download it here:
https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers

You can also find the code for this and previous posts here:
https://github.com/brodriguezv/sql-code

Final Thoughts

STRING_SPLIT() is a simple but powerful function that can greatly simplify stored procedures and queries that accept multiple values as parameters.

If you’re still maintaining legacy code with custom split functions, this is a great opportunity to refactor and improve performance.

Thanks for reading — see you soon 🍻