Select id in multi varchar field

Question

table category
id int
description varchar

table products
id int
id_cat varchar
etc..

id | id_cat
1  | 1, 2, 3
2  | 3,4,6,2
3  | 1, 5 , 7
.
.
.

I need something like

Select * from produts where id_cat in ( '1','2' )

but does not work!?

Answer 1

id | id_cat  
1  | 1, 2, 3

Your database layout does not allow having multiple categories per product -- it only allows one product category. What you need is an additional table to hold the product-category relations. Also, LIKEs are expensive to use, I would recommend using a JOIN instead.

Try this structure:

CREATE TABLE category (
    id INT PRIMARY KEY,
    description VARCHAR(255)
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    category_id INT,
    description VARCHAR(255)
);

CREATE TABLE product_category (
    product_id INT,
    category_id INT
);

You can then JOIN the tables, using SQL similar to this:

SELECT C.Name as "Category Name", P.Name as "Product Name"
FROM Product P
INNER JOIN ProductCategory PC ON P.ProductID = PC.ProductID
INNER JOIN Category C ON PC.CategoryID = C.CategoryID
ORDER BY C.Name, P.Name;

For a working example, see http://www.java2s.com/Code/Oracle/Table-Joins/GetCategoriesandProductswithJoins.htm

Answer 2

If you know that your numbers are ordered you could use something like this:

select * from products where id_cat like '%1,%2'

If not rather like this:

select * from products where id_cat like '1,%' AND id_cat like '% 2,%'

Another possibility, which will probably slow down the queries even further would be using regexes with rlike. The advantage is that more versatile expression would become possible.

Make sure your expressions do not accidentally find single digits in two digit numbers. This approach might work, but it's really not good practice to store values in that way. You rely on those number lists to have a fixed format, which can easily go boink with free text strings. Try redesigning your database if that's an option. Split the data in more tables, e.g., so that you can use int columns.

Answer 3

You can, using LIKE. You don't want to match for partial values, so you'll have to include the commas in your search. That also means that you'll have to provide an extra comma to search for values at the beginning or end of your text:

select 
  * 
from
  YourTable 
where 
  ',' || CommaSeparatedValueColumn || ',' LIKE '%,SearchValue,%'

But this query will be slow, as will all queries using LIKE, especially with a leading wildcard.

And there's always a risk. If there are spaces around the values, or values can contain commas themselves in which case they are surrounded by quotes (like in csv files), this query won't work and you'll have to add even more logic, slowing down your query even more.

A better solution would be to add a child table for these categories. Or rather even a separate table for the catagories, and a table that cross links them to YourTable.

https://stackoverflow.com/a/7212980/1235298