How can i split column value into different columns in SQL

Question

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

Will the column always contain exactly 3 two character groups? — Damien_The_Unbeliever, Jul 10 '12 at 07:01

Joe G Joseph · Answer 1 · 2012-07-10T11:42:16.707

0

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

edited Jul 10 '12 at 11:42

answered Jul 10 '12 at 07:15

Joe G Joseph

21,697
4
47
55

`ROW_NUMBER() over(order by (select 0))` is not guaranteed to generate the same row numbers for the same original rows. – Damien_The_Unbeliever Jul 10 '12 at 07:42
@Damien_The_Unbeliever : agree with you, thanks .. editing my answer in couple of minutes – Joe G Joseph Jul 10 '12 at 07:47
@Damien_The_Unbeliever: I have updated my query, Please chaeck now – Joe G Joseph Jul 10 '12 at 07:56
How Do I adapt this if I have four columns ? I like this one the best but I am not able to really adapt it if I had another column and I have the same scenario i.e. a,b,c,d – Vrashabh Irde Apr 05 '13 at 06:56

score 0 · Answer 2 · edited May 23 '17 at 11:48

0

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

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

edited May 23 '17 at 11:48

Community

1
1

answered Jul 10 '12 at 07:40

Ankit

5,983
7
46
74

Mikael Eriksson · Accepted Answer · 2012-07-13T06:19:09.887

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)

Hey, thanks for d reply, but i have 10 values with comma delimited , so in that how can i use it? — Harsh Patel, Jul 13 '12 at 06:02

score 0 · Answer 4 · answered Jul 10 '12 at 11:30

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

score 0 · Answer 5 · answered Jul 10 '12 at 11:38

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

How can i split column value into different columns in SQL

5 Answers5

Linked