Reputation: 20062

Mysql statement to select names that start with specific range of characters

I'm writing the following statement to select names that starts with a range of characters.

select name from db.table where name like '[c-e]%';

The statement does not return anything to me. But when I change it to:

select name from db.table where name like 'c%';

It returns records.

What is the problem ?

Upvotes: 0

Answers (4)

Reputation: 29081

You should use REGEXP as:

SELECT name FROM db.table where name REGEXP '[c-e].+';

Upvotes: 0

Reputation: 6317

@juergen d is right but a little change required as you want to check all the name starting from in range [c-d] so

select name from db.table where name REGEXP '^[c-e].+';

"^" indicates that match when starting must match range [c-e]

Upvotes: 2

Reputation: 62593

As others have said, you could use REGEXP; but when you want a range of starting characters, you can also do:

SELECT name FROM db.table WHERE name >= 'c' AND name < 'f';

This could be faster, as it can take advantage of an index in the name field to avoid a full scan.

Upvotes: 0

Reputation: 204756

instead of LIKE use REGEXP

select name from db.table where name REGEXP '[c-e].+';

Upvotes: 3