Question 194644 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL find multiple occurences LIKE '%pp%' AND LIKE '%p%' to NOT match "hippo"

I am trying to match keywords using multiple LIKE‘s, however I would like to be able to do something like this:

SELECT * FROM `keywords` WHERE keyword LIKE '%pp%' AND keyword LIKE '%p%';

to match "hippopotamus" but not "hippo".

I understand I can simply do:

SELECT * FROM `keywords` WHERE keyword LIKE '%pp%p%';

However I was wondering if there are any other approaches I could take. The above works fine for this example but for other examples Ill have several OR‘s such as:

LIKE '%pp%p%' OR LIKE '%p%pp%' OR LIKE '%pp%p%pp%

etc, and it will get very messy.

Does anyone know of a prettier way of doing this?

Tags:

2

Answers


  1. 0

    This can be achieved with REGEXP, like so:

    SELECT * FROM `keywords` WHERE keyword REGEXP '([^p]|^)p([^p]|$)' AND keyword LIKE '%pp%';

    Tests and explanation: (MySQL Workbench 8.0 CE)

    drop table if exists keywords;
create table keywords(
keyword varchar(50)
);
insert into keywords values("hippo");
insert into keywords values("hippopotamus");
insert into keywords values("zpp");
insert into keywords values("zp");
insert into keywords values("zpzpp");
insert into keywords values("zppzp");
insert into keywords values("zppzpzzzzzppzppzp");
insert into keywords values("p");
insert into keywords values("pp");
SELECT * FROM `keywords` WHERE keyword REGEXP '([^p]|^)p([^p]|$)'; -- Returns words with 'p' that isn't 'pp'
SELECT * FROM `keywords` WHERE keyword LIKE '%pp%'; -- Returns words with 'pp'
SELECT * FROM `keywords` WHERE keyword REGEXP '([^p]|^)p([^p]|$)' AND keyword LIKE '%pp%'; -- Combine to get desired effect
-- Should only get 'hippopotamus' 'zpzpp' 'zppzp' and 'zppzpzzzzzppzppzp'
    Login or Signup to reply.
  2. 0

    I’d suggest using MySQL REGEXP here, smth like this:

    WHERE keyword REGEXP '(?=^.*?[^p](p)[^p].*?$)(?=^.*?[^p](p{2})[^p].*?$)'

    haven’t tested, but it should be true only if p and pp are occured at the same time in any order

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search