Need simple way in TSQL to separate 1 column of data with embedded comma to 2 columns

• tom.wies

  SSC Enthusiast

  Points: 100

  More actions

  April 26, 2011 at 12:47 pm

  #389383

  Being somewhat new to SQLServer, I need a simple way in TSQL to separate 1 column of data with embedded comma to 2 columns

  COL1 is varchar(250) and data looks like:

  abc123 , xyz789

  a1 , z9

  b123456 , x99

  Need data in 2 columns of 1 table as follows:

  COLA

  abc123

  a1

  b123456

  COLB

  xyz789

  z9

  x99

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  April 26, 2011 at 12:55 pm

  #1316127

  DROP TABLE #Test

  CREATE TABLE #Test (Col1 VARCHAR(250))

  INSERT INTO #Test (Col1) VALUES ('abc123 , xyz789')

  INSERT INTO #Test (Col1) VALUES ('a1 , z9')

  INSERT INTO #Test (Col1) VALUES ('b123456 , x99')

  SELECT Col1,

  Col2 = PARSENAME(REPLACE(Col1,',','.'),2),

  Col3 = PARSENAME(REPLACE(Col1,',','.'),1)

  FROM #Test

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  April 26, 2011 at 12:55 pm

  #1316128

  How abt this?

  declare @table table

  ( col1 varchar(250))

  insert into @table (col1)

  values ('abc123 , xyz789') ,

  ('a1 , z9 ' ) ,

  ('b123456 , x99')

  select LEFT ( col1 , (charindex(',',col1) -1) ) ColA

  ,SUBSTRING (col1 , (charindex(',',col1)+1),250 ) ColB

  from @table

• tom.wies

  SSC Enthusiast

  Points: 100

  More actions

  April 26, 2011 at 2:07 pm

  #1316186

  Thank You. Works great.