CODEWITHSUNDEEP
mysqlsql
pedromillers
pedromillers

Reputation: 237

wildcard for single digit mysql

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.

Upvotes: 9

Views: 14670

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

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

Upvotes: 13

smuk
smuk

Reputation: 351

You can use REGEXP operator, see http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

so your query would be:

select name from table where name regexp 'CU[0-9].*';

Upvotes: 2

Guffa
Guffa

Reputation: 700562

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]'

Upvotes: 9

imbalind
imbalind

Reputation: 1182

Have you tried with:

select name from table where name between 'CU0' and 'CU9'

Upvotes: 1

Related Questions