CODEWITHSUNDEEP
mysqlbetween
John
John

Reputation: 10146

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

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?

Upvotes: 1

Views: 25

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

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.

As @John commented below, if you are not using a collation which is case sensitive, then we can simplify the above query to this:

SELECT *
FROM yourTable
WHERE name >= 'a' AND name < 'g';

Upvotes: 1

Shidersz
Shidersz

Reputation: 17190

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.

Upvotes: 1

Related Questions