4

I have a question about splitting column values in T-SQL. I have 

Address_col

Nevada,USA
Tokyo,Japan
Hanoi,Vietnam

I want to split the values by comma to 

Address_col     Country

Navada          USA
Tokyo           Japan
Hanoi           Vietnam

How can I do that?

Ivaylo Slavov
  • 8,839
  • 12
  • 65
  • 108
aliasosx
  • 1,175
  • 3
  • 10
  • 13
  • http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco – Kane Jan 28 '12 at 08:47

2 Answers2

5
declare @T table (Address_col varchar(20))

insert into @T values
('Nevada,USA'),
('Tokyo,Japan'),
('Hanoi,Vietnam')

select left(Address_col, charindex(',', Address_col)-1) as Address_col,
       stuff(Address_col, 1, charindex(',', Address_col), '') as Country
from @T

Update:
Split a string with three parts can look like this: declare @T table (Address_col varchar(20))

insert into @T values
('Nevada,USA,World'),
('Tokyo,Japan,World'),
('Hanoi,Vietnam,World')

select parsename(C, 3),
       parsename(C, 2),
       parsename(C, 1) 
from @T
  cross apply (select replace(Address_col, ',', '.')) as T(C)
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
1

If you only have two parts then this is the simplest I can think of.

select SUBSTRING(Address_col,1,CHARINDEX(',',Address_col)-1) as Address_col, SUBSTRING(Address_col,CHARINDEX(',',Address_col)+1,LEN(Address_col)) as Country

Neil Poulton
  • 56
  • 2