How to split multiple strings and insert SQL Server FN_SplitStr

I have 2 strings and one integer:

@categoryID int = 163,
@Ids nvarchar(2000) = '1,2,3',
@Names nvarchar(2000) = 'Bob,Joe,Alex'

I need to select 3 columns 3 rows; The most accomplished is 3 rows 2 columns:

select @categoryID,items from FN_SplitStr(@Ids,',')

resulting:

163,1
163,2
163,3

But I can't figure out how to split both strings.

I tried many ways like:

select @categoryID,items from FN_SplitStr((@Ids,@Names),',')
select @categoryID,items from FN_SplitStr(@Ids,','),items from FN_SplitStr(@Names,',')

EXPECTED OUTPUT:

163,1,Bob
163,2,Joe
163,3,Alex

NOTE1: I looked over tens of questions the most similar is: How to split string and insert values into table in SQL Server AND SQL Server : split multiple strings into one row each but this question is different.

NOTE2: FN_SplitStr is a function for spliting strings in SQL. And I'm trying to create a stored procedure.

4 answers

  • answered 2019-08-24 00:11 Neeraj Agarwal

    Inside your stored procedure do a string split of @Ids and insert into #temp1 table with an identity(1,1) column rowed. You will get:

    163,1,1
    163,2,2
    163,3,3
    

    Then do the second string split of @Names and insert into #temp2 table with an identity(1,1) column rowed. You will get:

    Bob,1
    Joe,2
    Alex,3
    

    You can then do an inner join with #temp1 and #temp2 on #temp1.rowid = #temp2.rowid and get:

    163,1,Bob
    163,2,Joe
    163,3,Alex
    

    I hope this solves your problem.

  • answered 2019-08-24 00:14 Avi

    Based on your expected output, you have to use cross apply twice and then create some sort of ranking to make sure that you are getting the right value. As IDs and Names don't seem to have any relationship cross apply will create multiple rows (when you split the string to Names and ID)

    There might be better way but this also gives your expected output. You can change this string split to your local function.

    1st Dense rank is to make sure that we get three unique names and 2nd dense rank is the rank within the name based on order by with ID and outside of the sub query you have to do some comparison to get only 3 rows.

    Declare @categoryID int = 163,
    @Ids nvarchar(2000) = '1,2,3',
    @Names nvarchar(2000) = 'Bob,Joe,Alex' 
    
    select ConcatenatedValue, CategoryID, IDs, Names from (
    select    concat(@categoryID,',',a.value,',',b.value) ConcatenatedValue, @categoryID CategoryID, 
    A.value as IDs, b.value  as Names , DENSE_RANK() over (order by b.value) as Rn, 
    DENSE_RANK() over (partition by b.value order by a.value) as Ranked  
    from  string_split(@IDs,',') a 
    cross apply string_split(@names,',') B ) t
    where Rn - Ranked = 0 
    

    Output:

    enter image description here

  • answered 2019-08-24 12:25 Gordon Linoff

    You can do this with a recursive CTE:

    with cte as (
          select @categoryId as categoryId,
                 convert(varchar(max), left(@ids, charindex(',', @ids + ',') - 1)) as id,
                 convert(varchar(max), left(@names, charindex(',', @names + ',') - 1)) as name,
                 convert(varchar(max), stuff(@ids, 1, charindex(',', @ids + ','), '')) as rest_ids,
                 convert(varchar(max), stuff(@names, 1, charindex(',', @names + ','), '')) as rest_names
          union all
          select categoryId,
                 convert(varchar(max), left(rest_ids, charindex(',', rest_ids + ',') - 1)) as id,
                 convert(varchar(max), left(rest_names, charindex(',', rest_names + ',') - 1)) as name,
                 convert(varchar(max), stuff(rest_ids, 1, charindex(',', rest_ids + ','), '')) as rest_ids,
                 convert(varchar(max), stuff(rest_names, 1, charindex(',', rest_names + ','), '')) as rest_names
          from cte
          where rest_ids <> ''
         )
    select categoryid, id, name
    from cte;
    

    Here is a db<>fiddle.

  • answered 2019-08-24 16:20 JIKEN

    You need to split CSV value with record number. For that you need to use ROW_NUMBER() function to generate record wise unique ID as column like "RID", while you split CSV columns in row. You can use table value split function or XML as used below.

    Please check this let us know your solution is found or not.

    DECLARE
        @categoryID int = 163,
        @Ids nvarchar(2000) = '1,2,3',
        @Names nvarchar(2000) = 'Bob,Joe,Alex'
    
    SELECT 
        @categoryID AS categoryID,
        q.Id,
        w.Names
    FROM
    (
        SELECT
            ROW_NUMBER() OVER (ORDER BY f.value('.','VARCHAR(10)')) AS RID, 
            f.value('.','VARCHAR(10)') AS Id
        FROM
        (
            SELECT 
                CAST('<a>' + REPLACE(@Ids,',','</a><a>') + '</a>' AS XML) AS idXML
        ) x
        CROSS APPLY x.idXML.nodes('a') AS e(f)
    ) q
    INNER JOIN
    (
        SELECT
            ROW_NUMBER() OVER (ORDER BY h.value('.','VARCHAR(10)')) AS RID,
            h.value('.','VARCHAR(10)') AS Names
        FROM
        (
            SELECT
                CAST('<a>' + REPLACE(@Names,',','</a><a>') + '</a>' AS XML) AS namesXML
        ) y
        CROSS APPLY y.namesXML.nodes('a') AS g(h)
    ) w ON w.RID = q.RID