Store multiple comma separated strings into temp table

Question

Given strings:

string 1: 'A,B,C,D,E'
string 2: 'X091,X089,X051,X043,X023'

Want to store into the temp table as:

String1     String2
---------------------
A           X091
B           X089
C           X051
D           X043
E           X023

Tried: Created user defined function named udf_split and inserting into table for each column.

DECLARE @Str1 VARCHAR(MAX) = 'A,B,C,D,E'
DECLARE @Str2 VARCHAR(MAX) = 'X091,X089,X051,X043,X023'

IF OBJECT_ID('tempdb..#TestString') IS NOT NULL DROP TABLE #TestString;

CREATE TABLE #TestString (string1 varchar(100),string2 varchar(100));

INSERT INTO #TestString(string1) SELECT Item FROM udf_split(@Str1,',');
INSERT INTO #TestString(string2) SELECT Item FROM udf_split(@Str2,',');

But getting following result:

SELECT * FROM #TestString

string1 string2
-----------------
A       NULL
B       NULL
C       NULL
D       NULL
E       NULL
NULL    X091
NULL    X089
NULL    X051
NULL    X043
NULL    X023

I think you need to change your split function to accept the two strings and return it. Could you post the function as well? — Ben Ootjers, Nov 25 '18 at 09:01

Zohar Peled · Accepted Answer · 2018-11-25T09:22:19.573

You need to insert both parts of each row together. Otherwise you end up with each row having one column containing null - which is exactly what happened in your attempt.

First, I would recommend not messing around with comma delimited strings in the database at all, if that's possible.
If you have control over the input, you better use table variables or xml. If you don't, to split strings in any version under 2016 I would recommend first to read Aaron Bertrand's Split strings the right way – or the next best way. IN 2016 you should use the built in string_split function.

For this kind of thing you want to use a splitting function that returns both the item and it's location in the original string. Lucky for you, Jeff Moden have already written such a splitting function and it's a very popular, high performance function.
You can read all about it on Tally OH! An Improved SQL 8K “CSV Splitter” Function.

So, here is Jeff's function:

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

and here is how you use it:

DECLARE @Str1 VARCHAR(MAX) = 'A,B,C,D,E'
DECLARE @Str2 VARCHAR(MAX) = 'X091,X089,X051,X043,X023'

IF OBJECT_ID('tempdb..#TestString') IS NOT NULL DROP TABLE #TestString;

CREATE TABLE #TestString (string1 varchar(100),string2 varchar(100));

INSERT INTO #TestString(string1, string2) 
SELECT A.Item, B.Item
FROM DelimitedSplit8K(@Str1,',') A
JOIN DelimitedSplit8K(@Str2,',') B
ON A.ItemNumber = B.ItemNumber;

The *built-in `STRING_SPLIT() (v2016+)` won't help, as it does not return the position... There is a very performant workaroud using `OPENJSON` by using `REPLACE`to create a JSON array from the CSV string first. — Shnugo, Nov 25 '18 at 09:48
@Shnugo Read Aaron Bertrand's [Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions](https://www.mssqltips.com/sqlservertip/5275/solve-old-problems-with-sql-servers-new-stringagg-and-stringsplit-functions/) - You can use a simple trick to get the order of the records. I thought about adding this link to my answer but since the OP is using 2008 r2 and your answer already popped up while I was busy writing mine, I thought I could leave it out.... — Zohar Peled, Nov 25 '18 at 09:54
@Shnugo BTW, I also though about not posting this at all after reading your answer but then decided that the links alone are good enough reason, and also the recommendation of not messing around with comma delimited strings.... — Zohar Peled, Nov 25 '18 at 09:58
Yeah, that reccomendation is always worth an answer :-) In this case this seems to be an external input - at least I got this impression... About the *trick with `STRING_AGG()`*: This is quite complicated and rather expensive. I'd prefer [this approach](https://stackoverflow.com/a/51401270/5089204). I like Jeff Moden's splitter, but it is limited to "8K"... — Shnugo, Nov 25 '18 at 10:17

Shnugo · Answer 2 · 2018-11-26T13:13:04.497

My suggestion uses two independant recursive splits. This allows us to transform the two strings in two sets with a position index. The final SELECT will join the two sets on their position index and return a sorted list:

DECLARE @str1 VARCHAR(1000)= 'A,B,C,D,E';
DECLARE @str2 VARCHAR(1000)= 'X091,X089,X051,X043,X023';

;WITH
 --split the first string
 a1 AS (SELECT n=0, i=-1, j=0 UNION ALL SELECT n+1, j, CHARINDEX(',', @str1, j+1) FROM a1 WHERE j > i)
,b1 AS (SELECT n, SUBSTRING(@str1, i+1, IIF(j>0, j, LEN(@str1)+1)-i-1) s FROM a1 WHERE i >= 0)
 --split the second string
,a2 AS (SELECT n=0, i=-1, j=0 UNION ALL SELECT n+1, j, CHARINDEX(',', @str2, j+1) FROM a2 WHERE j > i)
,b2 AS (SELECT n, SUBSTRING(@str2, i+1, IIF(j>0, j, LEN(@str2)+1)-i-1) s FROM a2 WHERE i >= 0)
--join them by the index
SELECT b1.n
      ,b1.s AS s1
      ,b2.s AS s2
FROM b1
INNER JOIN b2 ON b1.n=b2.n
ORDER BY b1.n;

The result

n   s1  s2
1   A   X091
2   B   X089
3   C   X051
4   D   X043
5   E   X023

UPDATE: If you have v2016+...

With SQL-Server 2016+ you can use OPENJSON with a tiny string replacement to transform your CSV strings to a JSON-array:

SELECT a.[key]
      ,a.value AS s1
      ,b.value AS s2
FROM OPENJSON('["' + REPLACE(@str1,',','","') + '"]') a
INNER JOIN(SELECT * FROM OPENJSON('["' + REPLACE(@str2,',','","') + '"]')) b ON a.[key]=b.[key]
ORDER BY a.[key];

Other than STRING_SPLIT() this approach returns the key as zero-based index of the element within the array. The result is the same...

Store multiple comma separated strings into temp table

2 Answers2

UPDATE: If you have v2016+...