SQL change "like" to "contains"

Posted by Paul on Stack Overflow See other posts from Stack Overflow or by Paul
Published on 2011-01-08T11:26:08Z Indexed on 2011/01/08 11:53 UTC
Read the original article Hit count: 159

Filed under:
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?

© Stack Overflow or respective owner

Related posts about sql