472,125 Members | 1,569 Online

# split color values into multiple records in sql server 2005

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!
Apr 25 '08 #1
6 1878
deepuv04
227 Expert 100+
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.

Expand|Select|Wrap|Line Numbers
1. DECLARE @COLURS VARCHAR( MAX)
2. SELECT @COLURS = 'brown - white - yellow -purple - blue'
3. --white - yellow
4. --green
5.
6. DECLARE @sSql VARCHAR(max)
7. SELECT @COLURS = REPLACE(@COLURS ,' ','')
8. SELECT @sSql = 'select ''' + REPLACE(@COLURS ,'-',''',''') + ''''
9.
10. SELECT @sSql
11.
12. EXEC (@ssql)
13.
thanks
Apr 25 '08 #2
hi,
try the following example. it might help you.

Expand|Select|Wrap|Line Numbers
1. DECLARE @COLURS VARCHAR( MAX)
2. SELECT @COLURS = 'brown - white - yellow -purple - blue'
3. --white - yellow
4. --green
5.
6. DECLARE @sSql VARCHAR(max)
7. SELECT @COLURS = REPLACE(@COLURS ,' ','')
8. SELECT @sSql = 'select ''' + REPLACE(@COLURS ,'-',''',''') + ''''
9.
10. SELECT @sSql
11.
12. EXEC (@ssql)
13.
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 ,:
Expand|Select|Wrap|Line Numbers
1. update customer
2. set LastName = SUBSTRING(name, CHARINDEX(', ', name, 1) + 2, LEN(name))
3. ,   FirstName = SUBSTRING(name, 1, CHARINDEX(', ', name, 1) - 1)
4.
5.
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!
Apr 25 '08 #3
deepuv04
227 Expert 100+
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 ,:
Expand|Select|Wrap|Line Numbers
1. update customer
2. set LastName = SUBSTRING(name, CHARINDEX(', ', name, 1) + 2, LEN(name))
3. ,   FirstName = SUBSTRING(name, 1, CHARINDEX(', ', name, 1) - 1)
4.
5.
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

Expand|Select|Wrap|Line Numbers
1.
2.
3. CREATE TABLE Colours ( id INT IDENTITY(1,1),colurs VARCHAR(MAX))
4.
5. INSERT INTO [Colours] VALUES ('brown - white - yellow -purple - blue')
6. INSERT INTO [Colours] VALUES('white - yellow')
7. INSERT INTO [Colours] VALUES(' green ')
8.
9.
10. DECLARE @sSql VARCHAR(max)
11. SELECT @sSql =''
12.
13. SELECT @sSql = @sSql + 'select ''' + REPLACE(colurs ,'-',''',''') + '''' FROM [Colours]
14.
15. --SELECT @sSql
16.
17. EXEC (@ssql)
18.
19.
here we can not union the results since there are different number of colours
Apr 25 '08 #4
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!
Apr 28 '08 #5
deepuv04
227 Expert 100+
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 :

Expand|Select|Wrap|Line Numbers
1.
2.
3.     DECLARE @I INT ,
4.         @colCount INT,
5.         @count int,
6.         @rowcount int
7.
8.     SELECT @I = 1
9.
10.     DECLARE @Color VARCHAR(max),
11.         @sSql VARCHAR(MAX),
12.         @val VARCHAR(100)
13.
14.     -- get number of rows
15.     SELECT @rowCount = count(*) from Colours
16.
17.     SELECT @sSql = ''
18.
19.         -- Loop through each rows
20.     WHILE ( @I <= @rowCount )
21.     BEGIN
22.         -- Get the colors of each row
23.         SELECT @Color = [colurs] FROM [Colours] WHERE ID = @I
24.
25.
26.         IF @Color != '' OR @Color IS NOT NULL
27.         BEGIN
28.
29.             -- Count of colors in each coloumn
30.
31.             SELECT @colCount  = (LEN(@Color) - LEN(REPLACE(@Color, '-', ''))) + 1
32.
33.
34.
35.             -- Construct dynamic sql
36.             SELECT @sSql = @sSql  +  ' insert into table_colors(id,'
37.
38.             SELECT @count = 1
39.             WHILE (@count <= @colCount)
40.             BEGIN
41.     --            select @count,@colCount
42.                 SELECT @sSql = @sSql + 'column' + CONVERT(VARCHAR(5),@count) + ','
43.                 SELECT @count = @count + 1
44.             END
45.
46.             SELECT @sSql = SUBSTRING( @sSql ,0,LEN(@sSql) ) + ') values (' + CONVERT(VARCHAR(5),@I) + ','
47.
48.             WHILE CHARINDEX('-',@color,1 ) != 0
49.             BEGIN
50.                 SELECT @Color = REPLACE(@Color,' ','')
51.                 --SELECT @val = SUBSTRING(@color,0,CHARINDEX('-',@color,1 ))
52.                 SELECT @sSql = @sSql + '''' + SUBSTRING(@color,0,CHARINDEX('-',@color,1 )) + ''','
53.                 SELECT @Color = SUBSTRING(@color,CHARINDEX('-',@color,1 ) + 1,LEN(@Color) )
54.     --            PRINT @Color
55.             END
56.     --        SELECT @sSql = SUBSTRING( @sSql ,0,LEN(@sSql) - 1 )
57.             SELECT @sSql = @sSql + '''' + @color + ''');'
58.         END
59.
60.         SELECT @I = @I + 1
61.
62.
63.
64.     END
65.
66.     -- Just to see the string generated
67.
68.     SELECT @sSql
69.
70.     --PRINT @sSql
71.
72.     -- Execute the query string to insert data into the table
73.     EXEC (@sSql )
74.
75.

thanks
Apr 29 '08 #6
Hi deepuv04,

Thank you very much! This code really helped me to solve my "color" problem!
Apr 29 '08 #7

### Post your reply

Sign in to post your reply or Sign up for a free account.

### Similar topics

 10 posts views Thread by shank | last post: by 4 posts views Thread by Roy Adams | last post: by 2 posts views Thread by Paul Hale | last post: by 7 posts views Thread by Greg P | last post: by 4 posts views Thread by Terren | last post: by 2 posts views Thread by db55 | last post: by 6 posts views Thread by Al Moodie | last post: by 3 posts views Thread by uma9 | last post: by 1 post views Thread by Fary4u | last post: by 1 post views Thread by beacampos | last post: by reply views Thread by pddon | last post: by reply views Thread by BenitoJuares | last post: by reply views Thread by bakertaylor28 | last post: by reply views Thread by leo001 | last post: by reply views Thread by antdb | last post: by 3 posts views Thread by bobbyer | last post: by 3 posts views Thread by Bright1Light | last post: by 7 posts views Thread by bounthong | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.