how to split the comma separated value into columns

Question

Source table:

Value |name

1     |clar,alwin,antony,rinu,dami,prince

expected output:

Value |  name1 |  name2 |  name3 | name4 |  name5 |  name6
1     | clar   | alwin  | antony | rinu  | dami   | prince

Tried this query:

 Select Substring(name, 1,Charindex(',', name1) as name1,
 Substring(name,1, Charindex(',', name)+1, LEN(name)) as  name2,
 Substring(name, Charindex(',', name)+2, LEN(name)) as  name3,
 Substring(name, Charindex(',', name)+3, LEN(name)) as  name4
 from tablename;

been asked many times before, perhaps try searching?! – Tanner Apr 12 '17 at 08:20 — Tanner, Apr 12 '17 at 08:20
did u even search for it (I am not the downvoter). – ahmed abdelqader Apr 12 '17 at 08:20 — ahmed abdelqader, Apr 12 '17 at 08:20

score 1 · Accepted Answer · answered Apr 12 '17 at 08:29

first create function to split values

create function [dbo].[udf_splitstring] (@tokens    varchar(max),
                                   @delimiter varchar(5))
returns @split table (
  token varchar(200) not null )
as



  begin

      declare @list xml

      select @list = cast('<a>'
                          + replace(@tokens, @delimiter, '</a><a>')
                          + '</a>' as xml)

      insert into @split
                  (token)
      select ltrim(t.value('.', 'varchar(200)')) as data
      from   @list.nodes('/a') as x(t)

      return

  end

SELECT 
max(CASE WHEN TOKEN='CLAR'   THEN TOKEN END) 'NAME1' ,
max(CASE WHEN TOKEN='ALWIN'  THEN TOKEN END) 'NAME2', 
max(CASE WHEN TOKEN='ANTONY' THEN TOKEN END) 'NAME3', 
max(CASE WHEN TOKEN='RINU'   THEN TOKEN END) 'NAME4', 
max(CASE WHEN TOKEN='DAMI'   THEN TOKEN END) 'NAME5', 
max(CASE WHEN TOKEN='PRINCE' THEN TOKEN END) 'NAME6'
FROM #Table1 as t1
CROSS APPLY [dbo].UDF_SPLITSTRING(name,',') as t2

output

NAME1   NAME2   NAME3   NAME4   NAME5   NAME6
clar    alwin   antony  rinu    dami    prince

Belarmino Vicenzo · Answer 2 · 2017-04-12T08:38:17.847

1

IDK if this will help you, but this is how I organized my csv files
PS: Accourding to your data

value,name1,name2,name3,name4,name5,name6
0,clar,alwin,antony,rinu,dami,prince

And when I use byulk insert you just need to say

BULK INSERT TableName FROM 'Location\filename.csv' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = ',', FIRSTROW = 2)

edited Apr 12 '17 at 08:38

answered Apr 12 '17 at 08:32

Belarmino Vicenzo

83
10

how to split the comma separated value into columns

2 Answers2