Split, Instr 1

[Dashley]

I have a column populated from a xml stream. In the column there are words deliminiated with a comma. could be two or more words, sometimes up to 6. Some rows contain a single word without any commas. I know how to select the rows that have commas but what I want to do it to take the rows that conatin commas and drop them into another table but split up based on the comma. Where the row containing ("Home, Home Improvement, Home Fixtures which is in one row, Select it and insert it into anoter table as three entries.

The select table Im selecting (catid, category)
as select catid and category from cattable where instr(category,',')
Any Ideas. Thanks

The insert table only has two colums (catid, category)

 

[markros]

There are many splitting functions available, take a look at a few here, for example:
Integer string splitting fable

Having a function in your possession, the code will be

insert into CatID, Category

select CatID, F.Value from
Categories CROSS APPLY dbo.fnSplit(Categories.Category,',') F

where fnSplit can be, for example:

CREATE FUNCTION [dbo].[fnSplit]
(@list  VARCHAR(8000),
 @delim CHAR(1) = ','
) RETURNS TABLE AS
RETURN
   WITH csvtbl(START, stop) AS (
     SELECT START = 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2, @list + @delim)
     UNION ALL
     SELECT START = stop + 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2,
                             @list + @delim, stop + 1)
     FROM   csvtbl
     WHERE  stop > 0
  )
  SELECT row_number() over (order by Start) as ID, LTRIM(RTRIM(SUBSTRING(@list, START,
                      CASE WHEN stop > 0 THEN stop - START ELSE 0 END)))
         AS VALUE
  FROM   csvtbl
  WHERE  stop > 0

GO

PluralSight Learning Library

 

[Dashley]

Looks Great Markros. Thank you. Of course I posted in the wrong Forum HA. I have to do this on MYSQl. Shouldnt be a big problem though. Thank you very Much!