CODEWITHSUNDEEP
sql
Paul
Paul

Reputation: 197

SQL change "like" to "contains"

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?

Upvotes: 3

Views: 1205

Answers (1)

Michał Turecki
Michał Turecki

Reputation: 3167

You can use LOCATE function:

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 could double the amount of conditions, or triple if second number can be first number of anouther 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: Final solution (long, after knowing that commas are separators):

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
)

Alternative (shorter, using REGEX, possibly slower)

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

You will need to escape | characters for REGEXP as this is a special character in regular expressions.

EDIT3:

REGEXP solution could be improved by using just one regular expression, such expressions are compiled during querying so single compilation is faster than 3 separate compilations even if matching expression is more complicated:

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

Upvotes: 1

Related Questions