STRING_SPLIT with a multiple-character separator?

Question

SQL Server 2016 introduced STRING_SPLIT which is very fast and an excellent replacement for any homemade implementation people would roll before 2016.

Unfortunately, STRING_SPLIT only supports a single-character separator, which isn't always enough. Does anyone know of a good implementation that allows for using multiple characters in the separator?

Answer 1

Well, you can always use REPLACE to add a single-character delimiter to the argument before passing it in. You just need to choose a character that is unlikely/impossible to appear in the actual data. In this example, let's say your original data uses three pipes as a delimiter; I chose a Unicode character at random to substitute:

DECLARE 
 @olddelim nvarchar(32) = N'|||', 
 @newdelim nchar(1) = NCHAR(9999); -- pencil (✏)
DECLARE @x nvarchar(max) = N'foo|||bar|||blat|||splunge';
SELECT * FROM STRING_SPLIT(REPLACE(@x, @olddelim, @newdelim), @newdelim);

I blogged about this in more detail here:

• Dealing with the single-character delimiter in STRING_SPLIT

---

Addressing a comment:

bad solution. what if original string is like 'abc||pqr|||rst||123' (dynamic and can contain anything). desired o/p is 'abc||pqr' and 'rst||123' but your solution will give 'abc' 'pqr' 'rst' '123'

Okay, let's take your input and see if my solution gets the wrong output.

DECLARE 
 @olddelim nvarchar(32) = N'|||', 
 @newdelim nchar(1) = NCHAR(9999); -- pencil (✏)
DECLARE @x nvarchar(max) = N'abc||pqr|||rst||123';
SELECT * FROM STRING_SPLIT(REPLACE(@x, @olddelim, @newdelim), @newdelim);

Result is:

abc||pqr
rst||123

And not what you must have assumed (but didn't test) this:

abc
pqr
rst
123

If your data is in a table, you could create a view so that you don't have to factor that expression into all of your queries.

---

If that doesn't work, because you might have a pencil (✏) in your data, and you can't find a single character in the 1,111,998 available Unicode characters that won't be in your data set, you'll have to skip STRING_SPLIT(), since it is hard-coded to accept a single character delimiter (separator Is a single character expression).

Alternatives have been answered here dozens of times before, many before STRING_SPLIT() existed. Those methods still work.

I go over many alternatives, and also discuss the limitations in STRING_SPLIT(), in this series (I also discuss why you might consider not doing this in T-SQL using any method at all):

• Split strings the right way – or the next best way
• Splitting Strings : A Follow-Up
• Splitting Strings : Now with less T-SQL
• Comparing string splitting / concatenation methods
• Performance Surprises and Assumptions : STRING_SPLIT()
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #2

• Peter Vandivier: Sometimes you just want to (mis)use string_split() ¯\_(ツ)_/¯
• Solomon Rutzky: @JitendraPancholi You are technically correct, but you're being unfair here because: 1) the limitation is with STRING_SPLIT, not this particular work-around, 2) this question is about STRING_SPLIT, not working with multi-character delimiters in general, 3) in practice, it's safe to assume that certain characters won't be there, else it's just bad data, 4) NCHAR(31) (record separator) should be safe since that is it's purpose, or NCHAR(0) with , @newdelim COLLATE Latin1_General_100_BIN2); because if U+0000 (null) is in any string data, then there are bigger problems!
• djk: @SolomonRutzky NCHAR(31) is unit separator; record separator is NCHAR(30); both are fine though.