PATINDEX to split a column into multiple columns

• zimiso

  SSC Veteran

  Points: 254

  More actions

  August 23, 2012 at 12:56 am

  #263218

  Hi,

  I am trying to split a column into multiple columns in sql 2008. This is what I have:

  CREATE TABLE ints

  (interests VARCHAR(99)

  );

  INSERT into ints VALUES ('skiiing, diving, driving' ),

  ('read, talk' ),

  ('swim, drive, walk, sleep' ),

  ('talk' );

  I want to split the column into 4 separate columns (interest_1……interest_4) as shown:

  Interest_1Interest_2Interest_3Interest_4

  skiing diving driving NULL

  read talk NULL NULL

  swim drive walk Sleep

  talk NULL NULL NULL

  I am trying to use PATINDEX:

  alter table ints

  add int1 varchar(20);

  update ints

  set int1 = SUBSTRING (interests,1,PATINDEX ('%,%',interests));

  But I do not get a value for the last (4th) row as show below:

  interests interest_1

  -------------------------------------------- -------------

  skiiing, diving, driving skiiing,

  read, talk read,

  swim, drive, walk, sleep swim,

  talk NULL

  (4 row(s) affected)

  Please HELP!!!!!!!!!!!!!!!!!!!!

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  August 23, 2012 at 1:36 am

  #1529070

  Homework?

  You don't get value because pattern you've specified for PATINDEX doesn't match the data in the 4th row - there is no comma.

  --Vadim R.

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  August 23, 2012 at 3:24 am

  #1529127

  I am doing this out of the top of my head and haven't tried this code, but something like :

  change:

  set int1 = SUBSTRING (interests,1,PATINDEX ('%,%',interests));

  To:

  set int1 = SUBSTRING (interests+',empty,empty,empty,empty',1,PATINDEX ('%,%',interests+',empty,empty,empty,empty'));

  I am not sure of the number of comma's and the number of empties, but something similar might solve your problem.

  Please let us hear if this helps or not.

  Ben brugman

• zimiso

  SSC Veteran

  Points: 254

  More actions

  August 23, 2012 at 4:23 am

  #1529151

  thanks, works good