Using a range on Postcode in sql

[Pinna]

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;