Mike Mike - 6 months ago 18
MySQL Question

Why does rlike and like in this case give different results?

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?

Answer

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 '^.*\\[.*\\]$';