Select a CSV string as multiple columns

6 votes

2 answers

14818 views

sql-server sql-server-2014 csv string-manipulation

                          I'm using SQL Server 2014 and I have a table with one column containing a [CSV] string:

    110,200,310,130,null

The output from the table looks like this:

 

I want to select the second column as *multiple columns*, putting each item of the CSV string in a separate column, like this:



So I created a function for splitting a string:

    create FUNCTION [dbo].[fn_splitstring]
    (
        @List nvarchar(2000),
        @SplitOn nvarchar(5)
    )  
    RETURNS @RtnValue table 
    (
        Id int identity(1,1),
        Value nvarchar(100)
    ) 
    AS  
    BEGIN 
        while (Charindex(@SplitOn,@List)>0)
        begin
            insert into @RtnValue (value)
            select 
                Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
    
            set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
        end
        insert Into @RtnValue (Value)
        select Value = ltrim(rtrim(@List))
    
        return
    END

I would like to use it similar to this:

    select Val , (select value from tvf_split_string(cchar1,','))  from table1

But the above code obviously won't work, because the function will return more than one row causing the subquery to return more than one value and breaking the code.

I can use something like:

    select Val ,
    (select value from tvf_split_string(cchar1,',') order by id offset 0 rows fetch next 1 rows only ) as col1,
    (select value from tvf_split_string(cchar1,',') order by id offset 1 rows fetch next 1 rows only ) as col2,
    ................
     from table1

but I don't think it's a good approach.

**What is the correct way to do it?**


                        

Asked by Moslem Hadi (163 rep)

May 1, 2017, 07:28 AM
Last activity: Jun 8, 2023, 03:16 PM

Select a CSV string as multiple columns

Related Questions