How can i split column value into different column

问题:

I have table test as

col_1
----------
aa,ab,ac
ba,bb,bc
ca,cb,cc

I want output as

col_1     col_2    col_3
-------------------------
aa        ab       ac
ba        bb       bc
ca        cb       cc

回答1:

declare @T table
(
  col_1 varchar(100)
)

insert into @T values
('aa,ab,ac,4,5,6,7,8,9,10'),
('ba,bb,bc,4,5,6,7,8,9,10'),
('ca,cb,cc,4,5,6,7,8,9,10')

select left(T.col_1, C1.Pos-1) as col_1,
       substring(T.col_1, C1.Pos+1, C2.Pos-C1.Pos-1)  as col_2,
       substring(T.col_1, C2.Pos+1, C3.Pos-C2.Pos-1)  as col_3,
       substring(T.col_1, C3.Pos+1, C4.Pos-C3.Pos-1)  as col_4,
       substring(T.col_1, C4.Pos+1, C5.Pos-C4.Pos-1)  as col_5,
       substring(T.col_1, C5.Pos+1, C6.Pos-C5.Pos-1)  as col_6,
       substring(T.col_1, C6.Pos+1, C7.Pos-C6.Pos-1)  as col_7,
       substring(T.col_1, C7.Pos+1, C8.Pos-C7.Pos-1)  as col_8,
       substring(T.col_1, C8.Pos+1, C9.Pos-C8.Pos-1)  as col_9,
       stuff(T.col_1, 1, C9.Pos, '') as col_10
from @T as T
  cross apply (select charindex(',', col_1)) as C1(Pos)
  cross apply (select charindex(',', col_1, C1.Pos+1)) as C2(Pos)
  cross apply (select charindex(',', col_1, C2.Pos+1)) as C3(Pos)
  cross apply (select charindex(',', col_1, C3.Pos+1)) as C4(Pos)
  cross apply (select charindex(',', col_1, C4.Pos+1)) as C5(Pos)
  cross apply (select charindex(',', col_1, C5.Pos+1)) as C6(Pos)
  cross apply (select charindex(',', col_1, C6.Pos+1)) as C7(Pos)
  cross apply (select charindex(',', col_1, C7.Pos+1)) as C8(Pos)
  cross apply (select charindex(',', col_1, C8.Pos+1)) as C9(Pos)

回答2:

You can use a function to split the string and the code will look like

Select dbo.Split(col_1,',') From test

回答3:

Hi if there are fixed no of columns,then we can use this below query.

DECLARE @col TABLE(col_1 varchar(255))

INSERT INTO @col
values('aa,ab,ac'),('ba,bb,bc'),('ca,cb,cc')

select * from @col

select LEFT(col_1,CHARINDEX(',',col_1,1)-1) as col_1,
LEFT(RIGHT(col_1,len(col_1)-CHARINDEX(',',col_1,1)),CHARINDEX(',',RIGHT(col_1,len(col_1)-CHARINDEX(',',col_1,1)),1)-1) as col_2,
RIGHT(col_1,2) as col_3
from @col

回答4:

Here is the solution even if there are more than 2 characters seperated by comma.

DECLARE @col TABLE(col_1 varchar(255))

INSERT INTO @col
values('aa,ab,ac'),('ba,bb,bc'),('ca,cb,cc')

select * from @col

select LEFT(col_1,CHARINDEX(',',col_1,1)-1) as col_1,
LEFT(RIGHT(col_1,len(col_1)-CHARINDEX(',',col_1,1)),CHARINDEX(',',RIGHT(col_1,len(col_1)-CHARINDEX(',',col_1,1)),1)-1) as col_2,
RIGHT(RIGHT(col_1,len(col_1)-CHARINDEX(',',col_1,1)),LEN(RIGHT(col_1,len(col_1)-CHARINDEX(',',col_1,1)))-CHARINDEX(',',RIGHT(col_1,len(col_1)-CHARINDEX(',',col_1,1)),1)) as col_3

from @col

回答5:

select substring(col_1,1,CHARINDEX(',',col_1,1)-1) as col_1, 
substring(col_1,(CHARINDEX(',',col_1,1)+1),CHARINDEX(',',col_1,CHARINDEX(',',col_1,1)+1)-(CHARINDEX(',',col_1,1)+1)) as col_2,
substring(col_1,CHARINDEX(',',col_1,CHARINDEX(',',col_1,1)+1)+1,LEN(col_1)) as  col_3
from test