The STRING_SPLIT() function is a powerful tool in SQL Server that allows you to easily manage parameters in T-SQL. Introduced in SQL Server 2016, this function is designed to quickly break apart a string using a single character delimiter. In this article, we will delve into the basics of how the STRING_SPLIT() function works and explore some practical use cases. We will also discuss its limitations and provide alternative solutions for splitting strings.

The Basics

The STRING_SPLIT function is a table-valued function, which means it can be used in place of a table in your code. You can use it in the FROM and JOIN clauses, just like you would with a regular table. The function takes two parameters: the string to be split and the separator. The syntax is as follows:

STRING_SPLIT(string, separator)

Let’s see the function in action with a simple example:

DECLARE @params VARCHAR(100) = '8,6,7,5,3,0,9';
SELECT *
FROM STRING_SPLIT(@params, ',') AS ss;

This will return:

value
------
8
6
7
5
3
0
9

The STRING_SPLIT() function returns a single column, “value”, which contains the split values. You can also use this column in the SELECT statement, like this:

SELECT ss.value
FROM STRING_SPLIT(@params, ',') AS ss;

Simple Splits

The string parameter can be of any character string type, including char, varchar, nchar, and nvarchar. If you pass in a Unicode type, the function will return Unicode results. If you pass in a non-Unicode type, you’ll get non-Unicode results. Let’s see some examples:

DECLARE @sentence VARCHAR(1000) = 'A room without books is like a body without soul.';
SELECT value FROM STRING_SPLIT(@sentence, ' ') AS ss;

This will split the sentence at each space and return:

value
------
A
room
without
books
is
like
a
body
without
soul.

You can choose any separator you want. For example, if you have a series of IP addresses and want to split them, you can use the period as a separator:

DECLARE @sentence VARCHAR(1000) = '192.168.1.210';
SELECT value AS 'IP Address'
FROM STRING_SPLIT(@sentence, '.') AS ss;

This will give you:

IP Address
----------
192
168
1
210

You can even choose other types of characters if you have some unusual data import that needs to be split, such as a phone number:

DECLARE @sentence VARCHAR(1000) = '303#4444#55555#666666';
SELECT value 
FROM STRING_SPLIT(@sentence, '#') AS ss;

This will return:

value
------
303
4444
55555
666666

Breaking Apart Parameters

One common use case for the STRING_SPLIT() function is to separate a list of values that you want to pass into a stored procedure or function. For example, suppose you have a list of orders in a table and you want to retrieve the details of orders that used a set of product codes. You can use the STRING_SPLIT() function to split the parameter and join it with the original table to find matching rows. Here’s an example:

CREATE PROCEDURE GetOrders
   @Orders varchar(1000) = NULL
AS
BEGIN
   IF @Orders IS NOT NULL 
      SELECT O.OrderItemID, O.ProductCode, O.SubProductCode, O.Quantity, O.UnitPrice
      FROM OrderItems O
      INNER JOIN STRING_SPLIT(@Orders, ',') S
      ON O.ProductCode = S.value
END

Now, you can pass a list of product codes to the stored procedure and get the corresponding order details:

DECLARE @orderlist VARCHAR(100) = 'SJ5A, LLAL,PC64';
EXEC GetOrders @orderlist;

This will return the orders that match the specified product codes.

A Few Limitations

While the STRING_SPLIT() function is a powerful tool, it does have some limitations. The biggest limitation is that it does not guarantee any ordering of the results. Without an ORDER BY clause, the function does not provide any ordering fields. You can order the results by the “value” column, but this will order the items in each row, not their original order in the parameter.

Another limitation is that the separator can only be a single character. This means you cannot split on multiple characters in a row or use regular expressions to split on two characters.

Lastly, the function requires string input. If you have array-based values or other data types, you need to convert them to a string and provide a matching separator.

Alternative Methods of Splitting Strings

While the STRING_SPLIT() function performs well in most situations, there are alternative methods for splitting strings that you can explore. These include the OPENJSON() function, CLR, and Jeff Moden’s string splitter. It’s important to test your workload and determine which method works best for your specific scenario.

Conclusion

The STRING_SPLIT() function is a valuable tool for splitting strings in SQL Server. It allows you to easily manage parameters and break them apart into a table structure that can be used with JOIN or APPLY operators. Despite its limitations, the function performs efficiently and is worth considering in situations where string splitting is required.

References:

• Books Online STRING_SPLIT() page
• Splitting Strings in SQL Server 2016
• Split strings the right way – or the next best way
• Reaping the Benefits of the Window Functions in T-SQL