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?
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:
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);
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 (
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