21

I have the following input:

Data
-----
A,10
A,20
A,30
B,23
B,45

Expected output:

col1  Col2
----  -----
A      10
A      20
A      30
B      23
B      45

How can I split the string to produce the desired output?

That1Guy
  • 7,075
  • 4
  • 47
  • 59
himadri
  • 213
  • 1
  • 2
  • 6

5 Answers5

45
SELECT substring(data, 1, CHARINDEX(',',data)-1) col1,
substring(data, CHARINDEX(',',data)+1, LEN(data)) col2
FROM table
niktrs
  • 9,858
  • 1
  • 30
  • 30
  • 1
    More info CHARINDEX http://msdn.microsoft.com/en-us/library/ms186323.aspx SUBSTRING http://msdn.microsoft.com/en-us/library/ms187748.aspx – niktrs Jul 08 '11 at 04:05
  • @nikrts sorry i posted my answer which is almost identical to you but i didnt copy your. – rahularyansharma Jul 08 '11 at 04:08
  • Nothing wrong with this solution, Wouldn't it be more appropiate to use 'left, 'right + reverse' with new logic instead of 'substring' ? – t-clausen.dk Jul 08 '11 at 10:43
  • I assumed that lenght can be variable eg. AB,51 in any other case I agree with @vlad – niktrs Jul 08 '11 at 11:03
  • check if if (CHARINDEX(',', data) > 0) to not get an error if there is no splitting character – live-love Dec 26 '19 at 16:28
5

I know the points has already been given, going to post it anyway because i think it is slightly better

DECLARE @t TABLE (DATA VARCHAR(20))

INSERT @t VALUES ('A,10');INSERT @t VALUES ('AB,101');INSERT @t VALUES ('ABC,1011')

SELECT LEFT(DATA, CHARINDEX(',',data) - 1) col1, 
RIGHT(DATA, LEN(DATA) - CHARINDEX(',', data)) col2
FROM @t
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
3

if the values in column 1 are always one character long, and the values in column 2 are always 2, you can use the SQL Left and SQL Right functions:

SELECT LEFT(data, 1) col1, RIGHT(data, 2) col2
FROM <table_name>
vlad
  • 4,748
  • 2
  • 30
  • 36
2
declare @string nvarchar(50)
set @string='AA,12'

select substring(@string,1,(charindex(',',@string)-1) ) as col1 
, substring(@string,(charindex(',',@string)+1),len(@string) ) as col2![my sql server image which i tried.][1]
rahularyansharma
  • 11,156
  • 18
  • 79
  • 135
0

it is so easy, you can take it by below query:

SELECT LEFT(DATA, CHARINDEX(',',DATA)-1) col1,RIGHT(Data,LEN(DATA)-(CHARINDEX(',',DATA))) col2 from Table
Mehdi najafian
  • 39
  • 3