sql: extracting values from a delimited string

[B@Th*nG]

hi guys,

I've built a table which has a list of , delimited products in it and the product category they belong to.

product cat | products
Productcat1| product1,product2,product3,product4


I want to extract the values in the products field and build a list of products and their relevant category, just to a temporary table like the below so I can concatenate the 2 fields:

Product | product cat
product1|productcat1
product2|productcat1
product3|productcat1

etc.. does anybody know how? I'm a massive noob at this so be gentle

Many thanks,

B@

 

[GravyMonster]

First of all, what type of DB is this? mySQL, MSSQL, Oracle?

Secondly, can you change the schema as storing a CDS in a column throws normalisation out of the window.

 

[philldutton]

If you're using SQL Server, something like this should do the trick. Agree with Spunkey about re-thinking your db design though.

declare @test table (
	productcat	char(100),
	products	varchar(2000)
);

insert into @test values (1, 'prod_a,prod_b,prod_c,prod_d,prod_e,prod_f,prod_g');

select t.productcat, o.split_products
from (
	select productcat, cast('<val>' + REPLACE(products, ',', '</val><val>') + '</val>' as xml) product_xml
	from @test
) t
cross apply (
	select prod_data.X.value('.','varchar(2000)') as split_products 
	from t.product_xml.nodes('val') as prod_data(X)
) o

 

[HungryHippos]

I had exactly the same problem a while back (though the delimited field was due to the source being delimited, not how I inserted the data). In the end I posted a question on Stack Overflow and got an answer that i've been using ever since!

Check it out:

http://stackoverflow.com/questions/...ted-values-in-a-sql-column-into-multiple-rows

Please note this is for MSSQL.

 

[Mailman86]

Instead of storing the data like that, you should do something like this (as long as a product can only ever have one category ie a one to many relationship):

Product
|Id|Name     |CategoryId|
|1 |Product 1|1         |
|2 |Product 2|2         |
|3 |Product 3|3         |

ProductCategory
|Id|Name      |
|1 |Category 1|
|2 |Category 2|
|3 |Category 3|

The id fields of both tables would be the table's auto generated primary keys (unique). The CategoryId column should have a foreign key relationship with the primary key in the ProductCategory table. When you want to find out what category a product is in, you could do a join of the two tables.

If a product could have many categories (ie a many to many relationship) you can do something with a third table to represent the data quite nicely.

If the reason you are inserting the data like you are is because that's how you get the data from an external source, I would do some processing on it before it's inserted to something like the above.