0

I have a mysql database that contains a range of postcode district ranges. I am trying to find the row that matches the given district, in this example 'SY18'. I have mysql query below, but it returns both the values in the table because the fields are alphanumeric.

SELECT rate FROM table WHERE district_from <= 'SY18' AND district_to >= 'SY18'

Table example

id    district_from    district_to    rate
1     SY1              SY9            10
2     SY16             SY22           20
3     AL1              AL99           37
4     B1               B99            37
5     BB1              BB99           37
6     CB1              CB99           40

How do I return only correct results?

Lightning_young
  • 86
  • 8
  • Normalize your codes by numeric part zerofill - i.e. not `SY1` but `SY01`. Then you may use BETWEEN operator. – Akina Oct 28 '20 at 10:53
  • @Akina I tried adding LPAD but I still get both results? `SELECT * FROM table WHERE CONCAT( LEFT(district_from, 2), LPAD(SUBSTRING(district_from, 3), 2, '0')) <= 'SY18' AND CONCAT( LEFT(district_to, 2), LPAD(SUBSTRING(district_to, 3), 2, '0')) >= 'SY18'` – Lightning_young Oct 28 '20 at 12:43
  • 1
    Does the prefix is **always** `SY`? Or maybe it may vary but **always** is 2-char? – Akina Oct 28 '20 at 12:43
  • @Akina I have updated the question with a few more examples in the table. Prefix can change any letter one or two characters. A1-A99 to X1-X99. – Lightning_young Oct 28 '20 at 13:23
  • A valid postcode district can also contain a letter at the end of the string, e.g. `WC2B` is the postcode district for `WC2B 4AB`. Do you need to consider any valid postcode district? – Dan Winchester Oct 28 '20 at 14:23

2 Answers2

0

You can use SUBSTR to skip first characters of the string and then +0 to consider the value as a number. Example:

SELECT v
FROM
(SELECT 'SY1' v UNION SELECT 'SY16') t
WHERE SUBSTR(t.v, 3)+0 > 10
;
Nae
  • 14,209
  • 7
  • 52
  • 79
  • I'm not sure this would work with my data. The table in the example is not complete and the data contains many text number combinations in this format A1-A99, AA1-AA99. – Lightning_young Oct 28 '20 at 12:47
0
CREATE FUNCTION normalize_district (district VARCHAR(4))
RETURNS VARCHAR(4) DETERMINISTIC
RETURN CONCAT( TRIM(TRAILING CASE WHEN 0 + RIGHT(district, 2) > 0 
                                  THEN RIGHT(district, 2)
                                  ELSE RIGHT(district, 1) END FROM district),
               LPAD(CASE WHEN 0 + RIGHT(district, 2) > 0 
                         THEN 0 + RIGHT(district, 2)
                         ELSE 0 + RIGHT(district, 1) END, 2, '0') );

and then

SELECT *
FROM district_to_test
LEFT JOIN rate ON normalize_district(district_to_test.district) 
                         BETWEEN normalize_district(district_from)
                             AND normalize_district(district_to);

fiddle

0 + RIGHT(district, 2) > 0 in the function thecks does the last 2 symbols in district are digits.

If true then 2-digit number wil be extracted by RIGHT(), and the whole expression value will be above zero (you claim that there is no values like 'AA0' or 'AA00').

If false, and only one digit is present, then RIGHT() will give a substring which is started from a letter, and the value will be zero.

Based on this I divide the whole value to a prefix and numeric postfix, and add a zero to the numeric part making it 2-digit unconditionally. Such value can be used for direct string comparing by BETWEEN operator.

The goal of a function is to convert the value to 'AA00' format. For this purposes we must divide the whole value to textual and numeric parts and normalize (zero-pad) the latter one.

You may use any other dividing method. For example, you may check does the value contains two digits using LIKE '%\\d\\d'. Or backward, you may determine does the value contains two letters using LIKE '\\D\\D%'... these methods have relatively equal difficulty.

Akina
  • 39,301
  • 5
  • 14
  • 25