CODEWITHSUNDEEP
mysql
user1227914
user1227914

Reputation: 3524

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?

Upvotes: 2

Views: 2287

Answers (1)

eggyal
eggyal

Reputation: 125985

What is the difference?

  • The regular expression

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

    This produces an error, because a quantifier (e.g. an unescaped * character) is not valid at the start of a group (i.e. immediately after an unescaped ( character). Ignoring the two times where that occurs yields:

    (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

Upvotes: 4

Related Questions