473,372 Members | 1,230 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

split color values into multiple records in sql server 2005

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
6 1971
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
siofok
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
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
siofok
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
siofok
4
Hi deepuv04,

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

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

Similar topics

10
by: shank | last post by:
I have a recordset that contains multiple records of product a user is purchasing. For clarity, I converted the recordset fields to variables. I need to take that entire recordset and insert it...
4
by: Roy Adams | last post by:
Hi forum I have three text fields set up on a page that when submitted I want them to be inserted in to a table but in differnt records at once. the code below only seems to insert the last field...
2
by: Paul Hale | last post by:
Hi all, being new to c# I'm trying to find the best way of passing multiple records to insert into a sql database via a stored procedure. I'm using visual studio 2005 RC SQL server 2005 and C# of...
7
by: Greg P | last post by:
I know this is a long post, please bear with me. I have been working on this all weekend to no avail although I have done a good amount of research (see most pertinent links that I've looked at...
4
by: Terren | last post by:
Is there a way to create one field from multiple records using sql. For example Table 1 John 18 Peter 18 David 18 Now I want an sql query that when executed will return a field that...
2
by: db55 | last post by:
I'm trying to debug/step through my t-sql code in SQL Server 2005 and I can't do it like I could in 2000. Any ideas?
6
by: Al Moodie | last post by:
Hi, I have a MySQL database where I want to updated multiple records. The table has two columns: product_number product_price I have a list with first entry product_price, second entry...
3
by: uma9 | last post by:
hi, the code below is used to insert a single record....i want to know how to insert multiple records using a "for" loop...please help Set connect = CreateObject ("ADODB.Connection")...
1
Fary4u
by: Fary4u | last post by:
how can i update multiple records corresponding to perticular ID & update values in one go ? mode_a contains ID No. Dim mode,mode_a,i mode=Request("t1") mode_a=split(mode,",")
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.