By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,362 Members | 1,344 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,362 IT Pros & Developers. It's quick & easy.

split color values into multiple records in sql server 2005

P: 4
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
Share this Question
Share on Google+
6 Replies


deepuv04
Expert 100+
P: 227
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

P: 4
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
Expert 100+
P: 227
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

P: 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
Expert 100+
P: 227
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

P: 4
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.