7

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?

17

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);

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):

  • 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' – Jitendra Pancholi Jan 21 at 11:20
  • @Jitendra Did you read my whole answer? You just need to choose a character that is unlikely/impossible to appear in the actual data. Unicode has 1,111,998 possible characters to choose from, but if you really can't find a single character that isn't in the data (or can't be replaced in the data first, during ETL or something), you'll need a different approach (like the many I linked to in my answer). Looking forward to your better solution. – Aaron Bertrand Jan 21 at 12:54
  • Still not a good one, you cannot be 100% sure about delimiter and assume that this delimiter would not be a part of string anyhow. – Jitendra Pancholi Jan 22 at 10:43
  • 1
  • 2
    @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! – Solomon Rutzky Jan 22 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.