sql - How to extract US zip codes and check range -

i have table of addresses postal codes , canada. in our system assign territories based on zip code ranges need extract addresses , check whether within given range. tables this:

key             postalcode --------------------------- 1               58230 2               49034-9731 3               98801 4               m5h 4e7 

i run select statement

select      key, convert(int, left(ltrim(rtrim(postalcode)),5)) pcode       table      left(postalcode, 5) not '%[^0-9]%' 

and results return table expected.

key             postalcode -------------------------- 1               58230 2               49034 3               98801 

i wrap alias , attempt check range.

select      key, pcode       (select           key, convert(int, left(ltrim(rtrim(postalcode)),5)) pcode                   table                 left(postalcode,5) not '%[^0-9]%') x        x.pcode between 58000 , 59000 

sql server 2008 returns error

msg 245, level 16, state 1, line 1
conversion failed when converting varchar value 'm5h 4' data type int.

can tell me happening? appears select alias doing scan of original table , encountering canadian postal code. appreciated.

if want pcode, use:

select key, pcode (select key,              (case when postalcode '[0-9][0-9][0-9][0-9][0-9]%'                    cast(left(postalcode, 5) int)               end) pcode       t      ) t t.pcode between 58000 , 59000; 

however, converting int unnecessary. cold use:

select key, left(postalcode, 5) table t postalcode '[0-9][0-9][0-9][0-9][0-9]%' ,       postalcode between '58000' , '59000'; 

it makes more sense treat postal codes strings rather numbers.

and, reason code doesn't work because sql server not guarantee order of evaluation of expressions in select , where. so, pushes calculation of pcode before filtering. thing performance perspective, consider bug. easy enough work around using case (which guarantee order of evaluation of conditions).