TSQL: multiple split string in one query

Question

I have a string_split function which looks like below

CREATE FUNCTION [dbo].[String_Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE ([value] varchar(8000))     
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       
      
    select @idx = 1       
        if len(@String)<1 or @String is null  return       
      
    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       
          
        if(len(@slice)>0)  
            insert into @temptable([value]) values(@slice)       
  
        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end

I am now successfully able to split the string using the below code

declare @NameList nvarchar(100) = 'Hi,Hi1,Hi2';
SELECT * FROM string_split(@NameList,',')

but now I want to split multiple string using the same function like below

declare @NameList nvarchar(100) = 'Hi,Hi1,Hi2';
declare @DESCLIST nvarchar(100) = 'Hii,Hii1,Hii2';

I want to split these strings in different columns,

my expected output is

col 1       col 2
------------------
 Hi     |Hii
 Hi1    |Hii1
 Hi2    |Hii2

how can I achieve this?

Answer 1

You can join them on row-number.

SELECT
  s1.value,
  s2.value
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM STRING_SPLIT(@NameList, ',') s
) s1
JOIN (
    SELECT *,
      rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM STRING_SPLIT(@DESCLIST, ',') s
) s2 ON s2.rn = s1.rn;

db<>fiddle

I strongly suggest you store multiple items in separate rows, rather than a comma-separated list. Consider using a table variable, temporary table or Table Valued Parameter.