How to split multiple strings and insert SQL Server FN_SplitStr

Question

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.

@Avi Updated the question witht expected output thanks for reminding me — Evik Ghazarian, Aug 23 '19 at 23:46

score 1 · Accepted Answer · answered Aug 24 '19 at 00:14

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:

score 0 · Answer 2 · answered Aug 24 '19 at 00:11

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.

score 0 · Answer 3 · answered Aug 24 '19 at 12:25

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.

score 0 · Answer 4 · answered Aug 24 '19 at 16:20

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

How to split multiple strings and insert SQL Server FN_SplitStr

4 Answers4