Search code examples
mysqlsqlregexsql-like

like clause doesn't work as it said in MySQL SQL

I have a table, such as create table table1( name varchar(32), );

And there's some data in it. When I select like this: select * from table1 where name like 'Jack2%';

there will be Jack2.

But if I select like this: select * from table1 where name like 'Jack[0-9]%'; there will be nothing;

And I also tried regexp to subsitute like, but it also didn't work!

What's wrong?

Solution

  • You've confused two different pattern-matching mechanisms. SQL LIKE uses % to match anything and _ to match any single character; it does not have anything like [0-9] to match a digit. That looks like a character class from a regular expression.

    Standard SQL has no support for regular expressions at all, but MySQL does - you just have to use RLIKE (or REGEXP, but that doesn't read as nicely IMO) instead of LIKE. But that means that you have to replace the % with the regular-expression equivalent .*, too.

    SELECT * FROM table1 WHERE name RLIKE 'Jack[0-9].*';