CODEWITHSUNDEEP
sqlsql-server
wilsonm2
wilsonm2

Reputation: 591

How can I delimit a field in SQL and create separate rows from that field?

I have a field in SQL Server that can sometimes contain text separated by Pipe like this:

COLOUR
Red
Blue|Red|Yellow
Green|White
Yellow

And I want to create this:

COLOUR          SUBCOLOUR
Red             Red
Blue|Red|Yellow Blue
Blue|Red|Yellow Red
Blue|Red|Yellow Yellow
Green|White     Green
Green|White     White
Yellow          Yellow

Anybody got any ideas?

Thanks

Upvotes: 0

Views: 82

Answers (2)

mohan111
mohan111

Reputation: 8865

by using split function we can achieve the desired result set 

 declare @t table (Colour  varchar(50))
    insert into @t (Colour)
    values 
    ('Red'),
    ('Blue|Red|Yellow'),
    ('Green|White'),
    ('Yellow')



 ;with cte as (
    SELECT  Colour As Colour ,
         Split.a.value('.', 'VARCHAR(100)') AS SubColour  
     FROM  (SELECT Colour,
             CAST ('<M>' + REPLACE([Colour], '|', '</M><M>') + '</M>' AS XML) AS String  
         FROM  @t) AS A CROSS APPLY String.nodes ('/M') AS Split(a))

         select * from cte

Upvotes: 1

Sam CD
Sam CD

Reputation: 2097

Untested, but with comments. Uses variables and a cursor, so would only recommend if this is not a large table and you are only doing this once. I would be happy to provide more explanation if this doesn't work for you: 

CREATE TABLE NewTable (Colour varchar(50),SubColour varchar(50))   

SELECT Colour, LEN(Colour) - LEN(REPLACE(Colour,'|','')) AS delimCount
INTO #temp FROM [ColourTable]

DECLARE @i int
DECLARE @delimLoc int
DECLARE @Colour varchar(50)
DECLARE @subColour varchar(50)
DECLARE ColourCursor CURSOR FOR SELECT * FROM #temp;
OPEN ColourCursor;

Going row by row through the table

WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM ColourCursor INTO @Colour;
SET @i = 1
SET @delimLoc = 0
IF delimCount > 0

BEGIN
WHILE @i <= delimCount

The loop creates a subcolor going from the location of the last used '|' (or the beginning of the Colour Field if @i = 1)

BEGIN
SET @subColour = SUBSTRING(@Colour,@delimLoc+1,CHARINDEX(@Colour,'|',@delimLoc+1))
INSERT INTO NewTable VALUES (@Colour,@subColour)
SET @i = @i + 1
SET @delimLoc = @delimLoc + CHARINDEX(@Colour,'|',@delimLoc+1)
END

END

END

CLOSE ColourCursor
DEALLOCATE ColourCursor;

Upvotes: 0

Related Questions