469,922 Members | 2,106 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,922 developers. It's quick & easy.

Using a range on Postcode in sql

1
Hiya, I am trying to select based on a range on postcode on a table. The field POSTCODE is a varchar2(8).

The problem is when refinng on 'where postcode between 'PE1' and 'PE29'', because this is a character field and not a number field, 'PE3','PE4','PE5','PE6','PE7','PE8','PE9' are all ignored because the construct orders in characters and then numbers and as a result the query excludes it.

Can anyone provide some code to overcome this taking into account all the variation of postcodes i.e WV14 5YT, WV4 6TR, B1 8JB etc... I managed to find the attached code but there is a variation of postcode that causes this to fail:

select decode(instr(upper(postcode),'B'),1 ,substr(postcode,1,1)||lpad(to_number(substr(subst r(postcode,1,length(postcode)-3),2,2)),2,'0')||
substr(postcode,length(postcode)-3,length(postcode)),
decode(instr(upper(postcode),'G'),1
,substr(postcode,1,1)||lpad(to_number(substr(subst r(postcode,1,length(postcode)-3),2,2)),2,'0')||
substr(postcode,length(postcode)-3,length(postcode)),
decode(instr(upper(postcode),'EC'),1
,substr(postcode,1,2)||substr(postcode,3,length(po stcode)-2),
decode(instr(upper(postcode),'NW'),1
,substr(postcode,1,2)||substr(postcode,3,length(po stcode)-2),
decode(instr(upper(postcode),'SW'),1
,substr(postcode,1,2)||substr(postcode,3,length(po stcode)-2),
decode(instr(upper(postcode),'SE'),1
,substr(postcode,1,2)||substr(postcode,3,length(po stcode)-2),
decode(instr(upper(postcode),'WC'),1
,substr(postcode,1,2)||substr(postcode,3,length(po stcode)-2),
decode(instr(upper(postcode),'L'),1
,substr(postcode,1,1)||lpad(to_number(substr(subst r(postcode,1,length(postcode)-3),2,2)),2,'0')||
substr(postcode,length(postcode)-3,length(postcode)),
decode(instr(upper(postcode),'M'),1
,substr(postcode,1,1)||lpad(to_number(substr(subst r(postcode,1,length(postcode)-3),2,2)),2,'0')||
substr(postcode,length(postcode)-3,length(postcode)),
decode(instr(upper(postcode),'S'),1
,substr(postcode,1,1)||lpad(to_number(substr(subst r(postcode,1,length(postcode)-3),2,2)),2,'0')||
substr(postcode,length(postcode)-3,length(postcode))
,substr(postcode,1,2)||lpad(to_number(substr(subst r(postcode,1,length(postcode)-3),3,2)),2,'0')||
substr(postcode,length(postcode)-3,length(postcode))))))))))))
from temp order by 1;
Aug 10 '07 #1
0 1812

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Lapchien | last post: by
reply views Thread by Fraser Dickson | last post: by
1 post views Thread by miz_luvly | last post: by
reply views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.