user1227914 user1227914 - 4 months ago 8
SQL Question

what is the difference between LIKE and REGEXP?

I have a mySQL wildcard query that isn't working probably because I use InnoDB instead of MyISAM.

SELECT a.product_id, a.category_id FROM products a
LEFT JOIN users u ON u.userid=a.ownerid
WHERE a.active=1 AND a.approved=1
AND a.deleted=0 AND a.name LIKE '%*my*%'
AND a.name LIKE '%*donuts*%'
AND (a.name REGEXP '( )*(*my*)*( )*(*donuts*)( )*')


It works fine whenever a word is used instead of a wildcard, that's not the problem.

I'm just wondering, is the part of

(a.name REGEXP '( )*(*my*)*( )*(*donuts*)( )*')


really needed after already doing a

LIKE '%*my*%' AND a.name LIKE '%*donuts*%'


What is the difference?

Answer

What is the difference?

  • The regular expression

    (a.name REGEXP '( )*(*my*)*( )*(*donuts*)( )*') 
    

    This matches any a.name with:

    1. zero or more spaces, followed by
    2. zero or more occurrences of:

      • the letter m, followed by
      • zero or more letters y

      followed by

    3. zero or more spaces, followed by
    4. the character sequence donut, followed by
    5. zero or more letters s, followed by
    6. zero or more spaces

     

  • The simple pattern

    a.name LIKE '%*my*%' AND a.name LIKE '%*donuts*%'
    

    This matches any a.name with:

    1. any sequence of characters, followed by
    2. the character sequence *my*, followed by
    3. any sequence of characters

    and with:

    1. any sequence of characters, followed by
    2. the character sequence *donuts*, followed by
    3. any sequence of characters
Comments