Search code examples
mysqlsqlregexsql-like

difference between like and regex operator

I'm learning MySQL now. I need your help in understanding the difference between these queries:

select id from tab where id like '000';

select id from tab where id regex '000';
Solution

  • Your first query uses like operator but does not use any wildcards. So it's equivalent to:

    select id from tab where id = '000';

    which lists only those id's where id is 000.

    The second query uses regex operator and it lists rows where id has 000 anywhere in it.

    Example: It'll list these id's: 1000,2000,000,0001

    To make your first query behave like the second you'll have to use wild card % which matches zero or more characters:

    select id from tab where id like '%000%';

    To make your second query behave like the fist you'll have to use start anchor(^) and end anchor($):

    select id from tab where id regex '^000$';