Data is a list in SQL table

[keneck]

I have inherited a problem. A SQL Server table contains several thousand records. Some of the data columns contain comma delimited lists for an individual record. Think of it as a badly designed shopping system in which all the details of an order are stored with the order itself. So under a column like ToolsOrdered, you might find a list of tool part numbers (98433, 49763). In another column (NumberOrdered), for the same record you find the number of each tool ordered (2,4). How can I get the details out so that I can work with the data? I need to know how many orders included tool # 98433 and how many of that tool were ordered. But it needs to be aggregated across lots of records.

I've worked with lists before, but never saw a list stored as a data item with the record. I need to be able to go over hundreds of records and extract the details across all those records. But with details stored as comma delimited lists I'm not sure how to get the data to a point where I can do some simple math.

Any suggestions would be appreciated. Thanks.

 

[r937]

the general strategy for this is to search not for 3 because that would return true for '12,13,14'

instead, search for the string ',3,'

however, since the comma-delimited string value in the column doesn't actually start or end with a comma, you have to concatenate commas both front and back

thus, if the column is called listcolumn and the value is '3,4,5' then you will find ',3,' if you search in CONCAT(',',listcolumn ,',')

in your examples, you had a space after the comma, so you have to use REPLACE to remove those

where concat(',',replace(listcolumn,' ',''),',') 
 like '%,[i]searchvalue[/i],%'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[r937]

i'm an idiot, i gave you mysql syntax, and you said sql server

where ',' + replace(listcolumn,' ','') + ',' 
 like '%,[i]searchvalue[/i],%'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)