SQL Server BETWEEN problem

Question

I have a table mapping postal code ranges to contacts. Postal codes are saved in multiple formats for different countries.

Table postcode-range:

FROM-CODE   TO-CODE    CONTACT-ID    COUNTRY-CODE
12000       12999      18            fr
BT000000    BT9ZZZZZ   34            uk
...

To find a contact in a specific range , e.g. range starting with 123, I use the following query:

select * from postcode-range
where '123' between from-code and to-code
and country-code = 'fr'

This will return the first entry, contact-id 18.

The problem is if I search for '120' or '12' I get no results.

Any idea what's wrong here? Anybody got an alternative SQL query?

Answer 1

'12' < '120' < '12000'

If you change your from and to codes to '12' and '12999' for 'fr' your query as written will work, although it would include patterns such as '121AB' which presumably isn't a valid French post code. You could include other validation such as testing that the string contains only numbers, or its length.

e.g. do

like '12[0-9][0-9][0-9]'

Answer 2

You could use LIKE '12%'
or use lpad to get the 1st 3 characters.

Answer 3

I found this solution:

select * from postcode-range
where country-code = 'fr' 
and left('12' + '00000000000000', len(from-code)) between from-code and to-code

If the query is shorter than the required length of the postal codes, then the missing characters are filled by zeros. I simply assume that no postal code is longer than 14 characters.

The country-code and query (here '12') are just place-holders.