split color values into multiple records in sql server 2005

[siofok]

Hi,

I have a table with color values. All these values differ from 1 color to 5 colors.
I.e.
brown - white - yellow -purple - blue
white - yellow
green

etc.

Now I want to seperate these values into different columns.
So insert the seperate color values into color1,color2,color3, color4, color5

I am a newbee, so please can someone show me the code for this problem?

Thanks in advance!

[deepuv04]

Hi,

I have a table with color values. All these values differ from 1 color to 5 colors.
I.e.
brown - white - yellow -purple - blue
white - yellow
green

etc.

Now I want to seperate these values into different columns.
So insert the seperate color values into color1,color2,color3, color4, color5

I am a newbee, so please can someone show me the code for this problem?

Thanks in advance!

hi,
try the following example. it might help you.

DECLARE @COLURS VARCHAR( MAX)
SELECT @COLURS = 'brown - white - yellow -purple - blue'
--white - yellow
--green
 
DECLARE @sSql VARCHAR(max)
SELECT @COLURS = REPLACE(@COLURS ,' ','')
SELECT @sSql = 'select ''' + REPLACE(@COLURS ,'-',''',''') + ''''
 
SELECT @sSql 
 
EXEC (@ssql)
 

thanks

[siofok]

hi,
try the following example. it might help you.

DECLARE @COLURS VARCHAR( MAX)
SELECT @COLURS = 'brown - white - yellow -purple - blue'
--white - yellow
--green
 
DECLARE @sSql VARCHAR(max)
SELECT @COLURS = REPLACE(@COLURS ,' ','')
SELECT @sSql = 'select ''' + REPLACE(@COLURS ,'-',''',''') + ''''
 
SELECT @sSql 
 
EXEC (@ssql)
 

thanks

Hi, thank you for your reply!

I tried it, and this works,
but when i set the @colurs to the table column, it only returns one result.

I have a whole table with rows containing colors.
like row 1: blue - white - yellow
row 2: brown - white
row 3: red - blue - white - orange - brown

i have a solution that works for only two values, seperated by a ,:

update customer
set LastName = SUBSTRING(name, CHARINDEX(', ', name, 1) + 2, LEN(name)) 
,   FirstName = SUBSTRING(name, 1, CHARINDEX(', ', name, 1) - 1) 
 
 

obvious this only works for 2 values.
I would like to have a script that splits up every value into the number of colors the original colum contains.

Thanks in advance!

[deepuv04]

Hi, thank you for your reply!

I tried it, and this works,
but when i set the @colurs to the table column, it only returns one result.

I have a whole table with rows containing colors.
like row 1: blue - white - yellow
row 2: brown - white
row 3: red - blue - white - orange - brown

i have a solution that works for only two values, seperated by a ,:

update customer
set LastName = SUBSTRING(name, CHARINDEX(', ', name, 1) + 2, LEN(name)) 
,   FirstName = SUBSTRING(name, 1, CHARINDEX(', ', name, 1) - 1) 
 
 

obvious this only works for 2 values.
I would like to have a script that splits up every value into the number of colors the original colum contains.

Thanks in advance!

Hi,
Here is a sample script, assuming the table has two columns id and color

 
 
CREATE TABLE Colours ( id INT IDENTITY(1,1),colurs VARCHAR(MAX))
 
INSERT INTO [Colours] VALUES ('brown - white - yellow -purple - blue')
INSERT INTO [Colours] VALUES('white - yellow')
INSERT INTO [Colours] VALUES(' green ')
 
 
DECLARE @sSql VARCHAR(max)
SELECT @sSql =''
 
SELECT @sSql = @sSql + 'select ''' + REPLACE(colurs ,'-',''',''') + '''' FROM [Colours] 
 
--SELECT @sSql 
 
EXEC (@ssql)
 
 

here we can not union the results since there are different number of colours

[siofok]

Great!

This gives the result i was looking for.
I have one question left.

How do I insert this result in the color table itself?
The original colors are in the color column (blue - green - white).
I would like each separated color inserted into the columns color1 color2....

Thank you very much!

[deepuv04]

Great!

This gives the result i was looking for.
I have one question left.

How do I insert this result in the color table itself?
The original colors are in the color column (blue - green - white).
I would like each separated color inserted into the columns color1 color2....

Thank you very much!

Hi,
since each row is having differenet number of colors, we need to build a dynamic query to insert colors into the colors table.

the script is as follows:

My logic is :
Here i am looping through each row, for each row take the number of colors in it, then construct the INSERT statement.

-- assuming the tables are:

CREATE TABLE Colours ( id INT IDENTITY(1,1),colurs VARCHAR(MAX))

CREATE TABLE table_colors
(
id INT,
column1 VARCHAR(100),
column2 VARCHAR(100),
column3 VARCHAR(100),
column4 VARCHAR(100),
column5 VARCHAR(100),
column6 VARCHAR(100),
column7 VARCHAR(100),
)


-- insert some dummy data


INSERT INTO [Colours] VALUES ('brown - white - yellow -purple - blue')

INSERT INTO [Colours] VALUES('white - yellow')

INSERT INTO [Colours] VALUES(' green ')

-- Script to generate dynamic sql :

 
 
    DECLARE @I INT ,
        @colCount INT,
        @count int,
        @rowcount int
 
    SELECT @I = 1
 
    DECLARE @Color VARCHAR(max),
        @sSql VARCHAR(MAX),
        @val VARCHAR(100)
 
    -- get number of rows
    SELECT @rowCount = count(*) from Colours 
 
    SELECT @sSql = ''
 
        -- Loop through each rows
    WHILE ( @I <= @rowCount )
    BEGIN
        -- Get the colors of each row
        SELECT @Color = [colurs] FROM [Colours] WHERE ID = @I
 
 
        IF @Color != '' OR @Color IS NOT NULL
        BEGIN
 
            -- Count of colors in each coloumn
 
            SELECT @colCount  = (LEN(@Color) - LEN(REPLACE(@Color, '-', ''))) + 1
 
 
 
            -- Construct dynamic sql
            SELECT @sSql = @sSql  +  ' insert into table_colors(id,'
 
            SELECT @count = 1
            WHILE (@count <= @colCount)
            BEGIN
    --            select @count,@colCount 
                SELECT @sSql = @sSql + 'column' + CONVERT(VARCHAR(5),@count) + ','
                SELECT @count = @count + 1
            END 
 
            SELECT @sSql = SUBSTRING( @sSql ,0,LEN(@sSql) ) + ') values (' + CONVERT(VARCHAR(5),@I) + ','
 
            WHILE CHARINDEX('-',@color,1 ) != 0
            BEGIN
                SELECT @Color = REPLACE(@Color,' ','')
                --SELECT @val = SUBSTRING(@color,0,CHARINDEX('-',@color,1 )) 
                SELECT @sSql = @sSql + '''' + SUBSTRING(@color,0,CHARINDEX('-',@color,1 )) + ''','
                SELECT @Color = SUBSTRING(@color,CHARINDEX('-',@color,1 ) + 1,LEN(@Color) ) 
    --            PRINT @Color 
            END
    --        SELECT @sSql = SUBSTRING( @sSql ,0,LEN(@sSql) - 1 )        
            SELECT @sSql = @sSql + '''' + @color + ''');'
        END 
 
        SELECT @I = @I + 1 
 
 
 
    END
 
    -- Just to see the string generated
 
    SELECT @sSql 
 
    --PRINT @sSql 
 
    -- Execute the query string to insert data into the table
    EXEC (@sSql )
 
 

thanks

[siofok]

Hi deepuv04,

Thank you very much! This code really helped me to solve my "color" problem!