wildcard for single digit mysql

Question

I want to use the LIKE operator to match possible values in a column.

If the value begins with "CU" followed by a digit (e.g. "3") followed by anything else, I would like to return it. There only seems to be a wildcard for any single character using underscore, however I need to make sure it is a digit and not a-z. I have tried these to no avail:

select name from table1 where name like 'CU[0-9]%'
select name from table1 where name like 'CU#%'

Preferably this could be case sensitive i.e. if cu or Cu or cU then this would not be a match.

Answer 1

You need to use regexp:

select name
from table1
where name regexp binary '^CU[0-9]'

The documentation for regexp is here.

EDIT: binary is required to ensure case-sensitive matching

Answer 2

The like operator only have the % and _ wildcards in MySQL, but you can use a regular expression with the rlike operator:

select name from table1 where name rlike '^CU[0-9]'