MySQL query field with range

Question

I've bought a zipcode database which stored all addresses' streetnumber in a sequence like this:

1002-1004;1101-1105;1102-1104;1201-1205;1202-1204;1301-1305;1302-1304;1401-1405;1402-1404;1501-1505;1502-1504;1601-1605;1602-1604;1701-1705;1702-1704;1801-1805;1802-1804;1902-1904;1901-1905;2007;2006-2012;2011-2013;2106-2112;2107-2113;2206-2212;2207-2213;2306-2312;2307-2313;2406-2412;2407-2413;2506-2512;2507-2513;2606-2612;2607-2613;2706-2712;2707-2713;2806-2812;2807-2813;2906-2912;2907-2913;3014-3020;3015-3021;3114-3120;3115-3121;3214-3220;3215-3221;3314-3320;3315-3321;3414-3420;3415-3421

This is the longest one, it also can be a shorter list, just a range or only one number. I'm trying to find a way to find an address by only using a zipcode and streetnumber.

Obviously I could split each entry in the database with a min / max value but this makes updating an even bigger task.

So is there a way that I can make this happen with a MySQL query? Or do I have to rely on PHP (in my case) here?

Eagerly awaiting...

Answer 1

There is no simple native function for checking if a numeric value (e.g. 1017) is within a set of ranges stored as a string like you have shown.

The short answer is, no, this kind of search can not be done in MySQL.

The longer answer is that is is possible, although impractical, to perform the kind of string manipulation required to pull out the individual min and max components of each of the ranges in that list.

It's impractical, first, from a performance standpoint (you would not want to do that kind of heavy lifting for every search. And it's impractical, secondly, due to the contorted SQL required to do that kind of string manipulation.

A much better approach to doing this type of search, from a MySQL performance standpoint, would be to represent that list of ranges as separate rows in a table.

r_mod r_min r_max
----- ----- -----
    0  1002  1004
    1  1101  1105
    0  1102  1104
    1  1201  1205

To get MySQL to "split" each of those range lists into a range on a separate row, with a trailing semicolon on each list, you could do a query something like this:

SELECT n.i
     , SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(d.foo,';', n.i ),';',-1),'-', 1 ),'-',-1)%2 AS r_mod
     , SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(d.foo,';', n.i ),';',-1),'-', 1 ),'-',-1)/1 AS r_min
     , SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(d.foo,';', n.i ),';',-1),'-', 2 ),'-',-1)/1 AS r_max
  FROM (SELECT '1002-1004;1101-1105;1102-1104;1201-1205;1202-1204;1301-1305;' AS foo) d
 CROSS
  JOIN ( SELECT h.d*100+t.d*10+o.d+1 AS i
           FROM (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
                 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
                 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) o
          CROSS
           JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
                  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t
          CROSS 
           JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
                  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) h
       ) n
HAVING r_max > 0

(Note that for that query, the range list string must have a trailing semicolon appended, and that query will only pull the first 1000 entries per list.) Given that kind of construct, it's theoretically possible to do a "search", using a numeric comparison:

avalue MOD 2 = r_mod AND avalue BETWEEN r_min AND r_max

Again, going back to the shorter answer, with the ranges stored in a table as a string list of ranges, it's not practical to do that type of string manipulation for each search.

With a pre-populated table with separate rows with each of those r_min and r_max values, it would be possible to do a search in MySQL.