mysqlregexsql-likerlikeregexp-like

Equivalency of RLIKE/REGEXP and LIKE - matching whole string vs. arbitrary substring

I have two queries.

SELECT count(AlbumID)
  FROM album
  WHERE albumname like '%[%]';

Result: 15733

SELECT count(AlbumName)
  FROM album
  WHERE AlbumName RLIKE '.*\\[.*\\]';

Result: 15740

So as you can see like returns 7 elements less than rlike. I have two questions why is that? Are the statements not the same? And if I am looking for names that contain lets say Result: Artist - Song [Live] or Result: Artist- Song [Gold CD, Excplicit Lyrics] what would query would return the correct result?

Solution

  • The exact equivalency of 

    LIKE '%[%]'

    is

    REGEXP '^.*\\[.*\\]$'

    (note that RLIKE is just a mSQL'ish synonym of REGEXP). In short that means that LIKE always matches the whole string, whilst REGEXP and RLIKE may match any substring.

    That is why I would assume that there are apparently seven AlbumNames around like

    MyName[abc]plus

    or

    MyName [abc]!

    You may try to determine these seven records by

    SELECT AlbumName FROM album 
WHERE AlbumName RLIKE '.*\\[.*\\]' AND AlbumName NOT RLIKE '^.*\\[.*\\]$';