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,c olor3, 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,c olor3, 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.

[code=sql]
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)
[/code]

thanks

[siofok]

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

[code=sql]
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)
[/code]

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

[code=sql]


CREATE TABLE Colours ( id INT IDENTITY(1,1),c olurs 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)

[/code]

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),c olurs 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 :

[code=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(@Co lor, '-', ''))) + 1



-- Construct dynamic sql
SELECT @sSql = @sSql + ' insert into table_colors(id ,'

SELECT @count = 1
WHILE (@count <= @colCount)
BEGIN
-- select @count,@colCoun t
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(@colo r,0,CHARINDEX('-',@color,1 ))
SELECT @sSql = @sSql + '''' + SUBSTRING(@colo r,0,CHARINDEX('-',@color,1 )) + ''','
SELECT @Color = SUBSTRING(@colo r,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 )

[/code]


thanks

[siofok]

Hi deepuv04,

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