CODEWITHSUNDEEP
mysqlregexrangewildcard
Maikel Koek
Maikel Koek

Reputation: 111

MySQL range selection with wildcards

Is it possible to select a database entry based on some of the first characters of a string, matching with a 'range' in a column with MySQL?

I'll try to make this more clear by an example: I need to select a database entry by matching a zipcode, but we retrieved the zipcodes from another system in 'ranges', such as 100-128. The zipcode 10001 (New York) needs to match this entry. The first 3 digits of the zipcode needs to match with 100 or 128, or anything in between (101, 102, ... 126, 127).

So, the database entries are all like 050-051, 144-146, 245-253 etc., which I need to match with a full zipcode.

Is it somehow possible to select this entry with a query?

Any help is appreciated!

Edit: I'll try to make it more clear:

I have just one zipcode, entered by a user, for example, 10001 (NY).

Now I want to retrieve all the entries of my shipping info table, which match with a range (100-128).

User zipcode: 07200, retrieve entries with range: 064-089

User zipcode: 33101, retrieve entries with range: 330-334

And so on.

My shipping info table has the following columns:

ID, country_id, zip_range, price

Upvotes: 0

Views: 468

Answers (1)

Barmar
Barmar

Reputation: 781721

Use SUBSTRING_INDEX to split the ranges apart into the beginning and end. Then match zipcodes whose first three characters are in that range with BETWEEN.

SELECT *
FROM shippingInfo
WHERE LEFT(:zipcode, 3) BETWEEN SUBSTRING_INDEX(ziprange, '-', 1) AND SUBSTRING_INDEX(ziprange, '-', -1);

:zipcode is a placeholder that gets the user-inputted zipcode.

Upvotes: 1

Related Questions