question

Good day,

As of SQL Server 2016 you have a built in function named STRING_SPLIT

With that being said, this question implies optionality of a very bad design of your relational database and you should really re-think about your database design or/and the type of the database which you chose to use

Hi @SQLWhisper-8590,

As @pituach already pointed out, it is better to use XML (or Json) for such cases.

Please try the following solution that is based on XML and XQuery.
It is a two step process:

1. CTE is composing a proper XML.
   

2. SELECT outputs desired relational/rectangular output.

The CTE emits XML structured as follows:

 <root>
   <r name="Mark" city="Dallas" />
   <r name="James" city="Houston" />
 </root>

SQL

 -- DDL and sample data population, start
 DECLARE @table table (sno INT, sname VARCHAR(200), sname1 VARCHAR(200))
 INSERT INTO @Table (sno,sname,sname1) VALUES 
 (1,'Mark|James','Dallas|Houston'),
 (2,'Mary|Katy|Paula','Miami|Fort Lauderdale|Orlando'); -- # of tokens could be dynamic
 -- DDL and sample data population, end
    
 DECLARE @separator CHAR(1) = '|';
    
 ;WITH rs AS
 (
  SELECT sno
  , TRY_CAST('<root><r>' + 
  REPLACE(sname + @separator + sname1, @separator, '</r><r>') + 
  '</r></root>' AS XML).query('<root>
  {
     let $counter := count(/root/r) div 2
     for $x in /root/r[position() le $counter]
     let $pos := count(/root/r[. << $x[1]]) + 1
     return <r name="{$x/text()}" city="{/root/r[$pos + $counter]/text()}"></r>
  }
  </root>') AS xmldata
  FROM @table
 )
 SELECT rs.sno
  , n.value('@name','VARCHAR(30)') AS [name]
  , n.value('@city','VARCHAR(30)') AS [city]
 FROM rs 
  CROSS APPLY xmldata.nodes('/root/r') AS t(n)
 ORDER BY rs.sno;

Output

 +-----+-------+-----------------+
 | sno | name  |      city       |
 +-----+-------+-----------------+
 |   1 | Mark  | Dallas          |
 |   1 | James | Houston         |
 |   2 | Mary  | Miami           |
 |   2 | Katy  | Fort Lauderdale |
 |   2 | Paula | Orlando         |
 +-----+-------+-----------------+

Hi :-)

According to your answer to my clarification question the following should solve your need (please confirm)

First I create table for test with more rows and options than the opn you provided

 DROP TABLE IF EXISTS T1
 GO
 CREATE table T1 (sno int,sname varchar(200),sname1 varchar(200))
 GO
    
 Insert into T1(sno,sname,sname1)
  Values 
  (1,'1A','1a'), (1,'2A|2B','2a|2b'), 
  (1,'3A|3B|3C','3a|3b|3c'), (1,'4A|4B|4C|4D','4a|4b|4c|4d')
 GO
    
 SELECT * FROM T1
 GO

And here is the solution

 ;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
  CROSS APPLY OPENJSON (s, N'$') t
 )
 SELECT sno, v1 , v2
 FROM MyCTE1
 CROSS APPLY (SELECT t1.[key] k2 , t1.[value] v2 FROM OPENJSON (s1, N'$') t1 where t1.[key] = MyCTE1.k1) t
 GO

Here is a solution that does not use XML or JSON (which both can result in problems if the data includes characters with a special function in these formats):

       declare @Table table (sno int,sname varchar(200),sname1 varchar(200))
            
      Insert into @Table(sno,sname,sname1)
      Values (1,'Mark|James','Dallas|Houston')
    
 SELECT t.sno, s.str + ' ' + s1.str
 FROM   @Table t
 CROSS  APPLY iter_charlist_to_tbl(t.sname, '|') AS s
 CROSS  APPLY iter_charlist_to_tbl(t.sname1, '|') AS s1
 WHERE  s.listpos = s1.listpos

You find the code for iter_charlist_to_tbl here: http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-strings

Yet a solution. This one is based on a performance test I did, and where I tried the solutions posted by Yitzhak, Ronen and me with a fairly large data set. I gave all ten minutes to run before I killed them. Looking at the plans, Ronen's is probably the one that would have completed first, but in how many hours, I don't know.

While I was waiting, I wrote a new solution which completed in three minutes. With a faster string splitter the time can be reduced further. But the keys is that the two strings need to split separately, so that we can join over an index.

SQLWhisper will have to accept that the names have changed. id = sno, ShipName = sname, ShipAddress = sname1

 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, s.listpos, s.nstr
   FROM   PipeData t
   CROSS  APPLY iter_charlist_to_tbl(t.ShipName, '|') AS s
    
 INSERT #t2(id, pos, ShipAddress)
   SELECT t.id, s.listpos, s.nstr
   FROM   PipeData t
   CROSS  APPLY iter_charlist_to_tbl(t.ShipAddress, '|') AS s
    
 SELECT t1.id, t1.ShipName + ' ' + t2.ShipAddress as str
 FROM   #t1 t1
 JOIN   #t2 t2 ON t1.id = t2.id
              AND t1.pos = t2.pos

For the curious, my test script is here: 34739-pipedatasplt.txt. The BigDB database that I load the data from is on http://www.sommarskog.se/present/BigDB.bak. Warning! This is a 3GB download, and the full database size is 20 GB. SQL 2016 or later is needed. (This is a demo database that I had uploaded already.)

Hi @SQLWhisper-8590,

In your situation, CLR function could be the best solution. You could take Adam's CLR function and compile the code to a DLL (using csc), and deployed just that file to the server if you would like to use CLR function.

Besides, XML is also good solution, you could refer other experts' queries.

I also found one good function DelimitedSplit8k which could also work for you.

  declare @table table (sno int,sname varchar(200),sname1 varchar(200))
        
  Insert into @Table(sno,sname,sname1)
  Values 
   (1,'1A','1a'), 
   (2,'2A|2B','2a|2b'), 
   (3,'3A|3B|3C','3a|3b|3c'),
   (4,'4A|4B|4C|4D','4a|4b|4c|4d'),
   (5,'5A|5B|5C|5D|5E','5a|5b|5c|5d|5e')
    
 select a.sno,s.item sname,s1.item sname1 from @Table a
 CROSS APPLY dbo.DelimitedSplit8k(a.sname,'|') s
 CROSS APPLY dbo.DelimitedSplit8k(a.sname1, '|') AS s1
 WHERE  s.itemnumber = s1.itemnumber

Output:

 sno    sname    sname1
 1    1A    1a
 2    2A    2a
 2    2B    2b
 3    3A    3a
 3    3B    3b
 3    3C    3c
 4    4A    4a
 4    4B    4b
 4    4C    4c
 4    4D    4d
 5    5A    5a
 5    5B    5b
 5    5C    5c
 5    5D    5d
 5    5E    5e

Regarding to the performance of multiple methods, you could refer Split strings the right way – or the next best way and choose the best for you.

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hi @ErlandSommarskog

The only reason your query is fast is related to the fact that you used temporary table and has nothing to do with your solution (your function).

In such table with such amount of data it is simply better to use two temporary table and then JOIN

I executed your query using your data but since my laptop is poor, in the PipeData table I only filled 10k rows using your query with top 10000

 CREATE DATABASE [ErlandTest]
     CONTAINMENT = NONE
     ON  PRIMARY ( NAME = N'ErlandTest', FILENAME = N'E:\SQL_Files\ErlandTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
     LOG ON ( NAME = N'ErlandTest_log', FILENAME = N'E:\SQL_Files\ErlandTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 GO
    
 USE [ErlandTest]
 go
    
 CREATE TABLE PipeData (id          int           NOT NULL, 
                        ShipName    nvarchar(MAX) NOT NULL, 
                        ShipAddress nvarchar(MAX) NOT NULL,
                        CONSTRAINT pk_PipeData PRIMARY KEY (id)
 )
 go

 -- THE ONLY DIFFERENT IS THAT I ADDED "top 10000"
 INSERT PipeData(id, ShipName, ShipAddress)
    SELECT top 10000 CustomerID, string_agg(convert(nvarchar(MAX), ShipName), '|') WITHIN GROUP (ORDER BY OrderID), 
                       string_agg(convert(nvarchar(MAX), ShipAddress), '|') WITHIN GROUP (ORDER BY OrderID)
    FROM   BigDB..BigOrders
    GROUP  BY CustomerID
 go

Next, I tested your solution on these 10k rows and it took more than 5 minutes.

I tested my solution (meaning using OPENJSON) using temporary tables, and guess what ?!?

As I expected my solution finished in 20 seconds!

Assuming I did not made a mistake in my test (and this is the expected result I had) the different is 20 seconds vs 5 minuets

So here is my query using temp table

 DROP TABLE IF EXISTS #t1
 DROP TABLE IF EXISTS #t2
 DROP TABLE IF EXISTS #temp4
 go
    
    
 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())
 go -- finished in 20410