How to get a match of results from MySQL database that are between characters?

3620 views mysql
6

Using MySQL how can I query for all records that start with a certain letter ranging from one letter to another? For example I want to find all entries that have the first letter between a-f.

Matches: Albert Donald Frank

Non Matches: Sarah Reba Thomas

With numbers I can use

SELECT * FROM table WHERE id >= int AND id <= int

Or use the between statement. How can I do that using letters using the first letter of each word in the database?

answered question

3 Answers

9

select * from your_table
where name regexp '[a-f].+'

posted this
11

You should be able to use a range here. To cover a through and including f, regardless of case, we can try:

SELECT *
FROM yourTable
WHERE (name >= 'a' AND name < 'g') OR (name >= 'A' AND name < 'G');

Demo

Note that this approach leaves open the possibility of MySQL being able to use an index which might exist on the name column.

posted this
2

You can use regular expression for this. Read more here: REGEX_LIKE(). The query you need will be like this:

MySQL 8.0

SELECT
    *
FROM
    <table_name>
WHERE
    REGEXP_LIKE(<column_name>, '^[a-f]');

MySQL < 8.0

SELECT
    *
FROM
    <table_name>
WHERE
    <column_name> REGEXP '^[a-f]';

This will match all register with starting with [a to f] range letters.

posted this

Have an answer?

JD

Please login first before posting an answer.