You are here:   Blog
Register   |  Login

Blog Archive:

* Can be used in order to search for older blogs Entries

Search in blogs

Blog Categories:

* Can be used in order to search for blogs Entries by Categories

Blog Tags:

* Can be used in order to search for blogs by keywords


Awared MVP


Microsoft® Community Contributor 

Microsoft® Community Contributor

 Read this before you use the blog! Maximize

Recent Entries


Written by: ronen ariely
25/10/2020 08:53 RssIcon


Before the release of SQL Server 2016, one of the most required solution was for a function which split string. There are many solutions you can find online. Most of these are based on loops that provide poor performance, using XML, or using SQLCLR which in my test provides the best option.

After many years of requests for such a function, Microsoft has finally provided us with a new function called STRING_SPLIT in SQL Server 2016, which should solve our need to split a string. Unfortunately this solution is a half solution at best!

In most cases we need a solution which guarantee the order of the result set. The STRING_SPLIT does not provide this. Thus from 2016, the requests to provide a function for splitting string, has been replaced by shouts to improve the existing function by adding column which present the order of the text.

Meanwhile, most users continue to work with the old solutions that provide poor performance, except for the SQLCLR user solution which provides excellent performance but is complex to implement for most users.

I am wondering why in 2019 so many people are fixed to use the "official function" of splitting string STRING_SPLIT and when it does not fit, thenimmediately jump to old solutions which provide poor performance. I just came across another question in the Microsoft QnA forum that is related to this topic, and a google search did not yield the simple solution which is right under our eyes. 

So.. what that "perfect solution"?!?

I do not like to use the term perfect since each solution might fit best for different scenario, but for most cases all we need to do is using a different approach using another function which was presented in SQL Server 2016 named OPENJSON.



This solution only fit for cases we do not care about the order of the result set!

-- Using string_split
declare @String NVARCHAR(1000)
SET @String = 'This@Is@My@String@To@Split'
SELECT * from STRING_SPLIT (@String, '@')
GO -- The problem is that the order of the result is not guaranteed

More technics to split the string without order guarantees can be found here: https://sqlperformance.com/2016/03/sql-server-2016/string-split

But we want to have the order guaranteed! So lets check the solutions


Using XML

Based on solution by Mikael Eriksson at stackoverflow

Option one

declare @String NVARCHAR(1000)
SET @String = 'This@Is@My@String@To@Split'
SET @X = cast('' + replace(@String, '@', '') + '' AS XML)
-- T.c is the node and it has order in the node() function
select T.c.value('.', 'varchar(max)') as q, row_number() over(order by T.c) as rn
from @X.nodes('i') T(c)

The order of the nodes returned by the method nodes() is guaranteed but we must have a column in the result set which include the order value or the result of the total query will not be guarantee since without ORDER BY the order is not guaranteed.


Option two

For this solution we will use numbers table

;WITH cteN(Number) AS(
  SELECT ROW_NUMBER() OVER (ORDER BY (select null)) - 1
  FROM sys.all_columns AS s1
  CROSS JOIN sys.all_columns AS s2
SELECT [Number] INTO dbo.Numbers
FROM cteN WHERE [Number] <= 1000000
  FILLFACTOR = 100,      -- in the event server default has been changed
  DATA_COMPRESSION = ROW -- if Enterprise & table large enough to matter

and the solution is

declare @String NVARCHAR(1000)
SET @String = 'This@Is@My@String@To@Split'
SET @X = cast('' + replace(@String, '@', '') + '' AS XML)
select T.c.value('.','varchar(max)') as q, N.Number
from Numbers as N
cross apply @X.nodes('i[sql:column("N.Number")]') as T(c)
where N.Number between 1 and @X.value('count(i)', 'int')


Using OPENJSON to the rescue

-- Using OPENJSON We can split with order guaranteed
declare @String NVARCHAR(1000)
SET @String = 'This@Is@My@String@To@Split'
SELECT [key],[value] from OPENJSON ('["' + REPLACE(@String,'@','","') + '"]')
GO -- and we have the "key" which porovide a guaranteed order

Let's take this approach to the next level and show some more complex solutions...


Splitting Strings in Multiple Columns and keeping the same order

I need a better title for this... Any how let me explain what is our goal



We have a table with multiple columns that each stores a string. We need to split these strings into rows of substrings, based on a specified separator character. But we must keep the same order of substrings o fit other columns order of substrings.

 CREATE table T1 (sno int,sname varchar(200),sname1 varchar(200))
 Insert into T1(sno,sname,sname1)
  (1,'1A','1a'), (1,'2A|2B','2a|2b'),
  (1,'3A|3B|3C','3a|3b|3c'), (1,'4A|4B|4C|4D','4a|4b|4c|4d')

For example, the second row in the above table should produce 2 rows in the result set since in column sname and column sname1 we have a string with a single separator character "|". This can achieved using STRING_SPLIT if the order was not important but in opur case we want in the new rows to have the value "2A" together with the value "2a" and the values "2B" mnust come in the same row as the value "2b" since these substring locates in the same order in the original string


The Solution

We can use OPENJSON to split the each column and since OPENJSON provide a key which present the order of the substring, we can use simple filter to chose have the right substring from each source string in the same order.

;With MyCTE0 as (
    select sno, sname,sname1
        ,s = '["' + Replace(sname, '|', '","') + '"]'
        ,s1 = '["' + Replace(sname1, '|', '","') + '"]'
    from T1
MyCTE1 as (
    select sno, s, s1
        , k1 = t.[key], v1 = t.[value]
    from MyCTE0
SELECT sno, v1 , v2
CROSS APPLY (SELECT t1.[key] k2 , t1.[value] v2 FROM OPENJSON (s1, N'$') t1 where t1.[key] = MyCTE1.k1) t


So, as you can see the solution is not complex and the performance are OK, but who wants to compromise on OK?!? I want the best 🤣

Well... to be true, when you work with SQL Server then you always must remember that for best solution, One has to choose the right solution for a particular situation and there is no one solution that will always be the best. The answer for most most question is: It is It depend

Using the solution above might fit for most cases but when dealing with large amount of data with many rows and when the text we need to split might  might include a lot of sub parts, then we need a bit different approach...

Using Temporarily table

The trigger for writing this post was a question asked at Microsoft QnA forum. It is highly recommended to check the thread. There are many interesting comment and several solutions which were presented there. A test which was done on the different solution shows that my query run almost 20 times faster then other solution which was based on looping.

 DECLARE @d datetime2 = sysdatetime()
 CREATE TABLE #t1 (id  int NOT NULL,
                   pos int NOT NULL,
                   ShipName nvarchar(60) NOT NULL,
                   PRIMARY KEY (id, pos))
 CREATE TABLE #t2 (id  int NOT NULL,
                   pos int NOT NULL,
                   ShipAddress nvarchar(60) NOT NULL,
                   PRIMARY KEY (id, pos))
 INSERT #t1(id, pos, ShipName)
  SELECT t.id,[key] listpos ,[value] nstr
  from PipeData t
  CROSS APPLY OPENJSON ('["' + REPLACE(t.ShipName,'|','","') + '"]') AS s
 INSERT #t2(id, pos, ShipAddress)
  SELECT t.id,[key] listpos ,[value] nstr
  from PipeData t
  CROSS APPLY OPENJSON ('["' + REPLACE(t.ShipAddress,'|','","') + '"]') AS s
 SELECT t1.id, t1.ShipName, t2.ShipAddress as str
 INTO   #temp4
 FROM   #t1 t1
 JOIN   #t2 t2 ON t1.id = t2.id
              AND t1.pos = t2.pos
 SELECT datediff(ms, @d, sysdatetime())


Read More