SQL change "like" to "contains"

Question

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 purpose of this question). The request is sent to certain categories, each in xxxx format | yyyy, and I need to return products that have a past category to my comma-separated list of categories.

The current request looks like this:

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 allows anomalies. I would like to write a request more:

select p.* from products p 
where (p.categories contains '27|0' or p.categories contains'972|1' or p.categories contains  '969|1')

How should I do it?

Answer 1

You can use LOCATE :

select p.* from products p 
where (LOCATE('27|0', p.categories) > 0 or LOCATE('972|1', p.categories) > 0 or LOCATE('969|1', p.categories) > 0))

EDIT: if you want to avoid false positives, you can double the number of conditions, or triple if the second number can be the first number in the anouter category:

select p.* from products p 
where (
  LOCATE('27|0', p.categories) = 1 or LOCATE(',27|0', p.categories) > 0 or 
  LOCATE('972|1', p.categories) = 1 or LOCATE(',972|1', p.categories) > 0 or
  LOCATE('969|1', p.categories) = 1 or LOCATE(',969|1', p.categories) > 0
)

EDIT2: ( , , ):

select p.* from products p 
where (
  p.categories = '27|0' OR LOCATE('27|0,', p.categories) = 1 or LOCATE(',27|0,', p.categories) > 0 or LOCATE(',27|0', p.categories) = CHAR_LENGTH(p.categories) - CHAR_LENGTH(',27|0') + 1 or
  p.categories = '972|1' OR LOCATE('972|1,', p.categories) = 1 or LOCATE(',972|1,', p.categories) > 0 or LOCATE(',972|1', p.categories) = CHAR_LENGTH(p.categories) - CHAR_LENGTH(',972|1') + 1 or
  p.categories = '969|1' OR LOCATE('969|1,', p.categories) = 1 or LOCATE(',969|1,', p.categories) > 0 or LOCATE(',969|1', p.categories) = CHAR_LENGTH(p.categories) - CHAR_LENGTH(',969|1') + 1
)

(, REGEX, , )

select p.* from products p WHERE
  p.categories REGEXP '(,|^)27\|0(,|$)'
  or p.categories REGEXP '(,|^)972\|1(,|$)'
  or p.categories REGEXP '(,|^)969\|1(,|$)'

| REGEXP, .

EDIT3:

REGEXP , , , , 3 , :

select p.* from products p WHERE p.categories REGEXP '(,|^)(27\|0|972\|1|969\|1)(,|$)'