SQL change "like" to "contains"
- by Paul
products table (mySQL)
record_id categories (comma-delimited list)
--------- --------------------------------
1 960|1,957|1,958|1
I have the following dynamic query (simplified for the purposes of this question). The query is passed specified categories, each in the format xxxx|yyyy, and I need to return products having the passed category in its comma-delimited list of categories.
The current query looks like:
select p.* from products p
where (p.categories like '%27|0%' or p.categories like '%972|1%' or p.categories like '%969|1%')
But, the LIKE clause sometimes permits anomalies.
I would like to write the query more like:
select p.* from products p
where (p.categories contains '27|0' or p.categories contains'972|1' or p.categories contains '969|1')
How would I do this?
