Need to seperate string in two columns using T-SQL

• Raj Malwade

  Mr or Mrs. 500

  Points: 594

  More actions

  February 9, 2005 at 9:29 am

  #60979

  Hi:

  I have a VARCHAR(80) column in a SQL Server Database. Column can have values like:

  Column1

  Americas - NorthEast

  Americsa - SouthEast

  Europe - North Region

  Asia - SouthEast

  How do I write a SQL statement which would give me result in two columns. Like:

  Column1             Column2

  Americas             NorthEast

  Americas             SothEast

  Europe                North Region

  Asia                    SouthEast

  If using AxtiveX script with DTS is easier, please let me know how it would work.

  Thanks,

  Raj

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  February 9, 2005 at 9:33 am

  #541381

  See the CHARINDEX() and SUBSTRING() functions in BOL.

  Part1: Substring(YourColumn, 1, CharIndex(' - ', YourColumn) - 1)

  Part2: SubstringYourColumn, CharIndex(' - ', YourColumn) + 3, 80)

   

• Wayne Lawton

  Hall of Fame

  Points: 3532

  More actions

  February 9, 2005 at 9:41 am

  #541385

  create table testtable (Col1 varchar(50), Col2 varchar(50), Col3 varchar(50))

  go

  insert into testtable (Col1)

  SELECT 'Americas - NorthEast'

  UNION

  SELECT 'Americsa - SouthEast'

  UNION

  SELECT 'Europe - North Region'

  UNION

  SELECT 'Asia - SouthEast'

  GO

  SELECT * FROM TESTTABLE

  update testtable

     SET COL2 = LEFT(COL1,PATINDEX('% %', COL1)),

         COL3 = RIGHT(COL1,LEN(COL1)-(PATINDEX('%-%', COL1)+1))

  GO

  SELECT * FROM TESTTABLE

  Hope this helps

  Wayne