Btukfyl

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    

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;

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...