472,353 Members | 2,117 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

Using a range on Postcode in sql

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,1,1)||lpad(to_number(substr(subst r(postcode,1,length(postcode)-3),2,2)),2,'0')||
,substr(postcode,1,2)||substr(postcode,3,length(po stcode)-2),
,substr(postcode,1,2)||substr(postcode,3,length(po stcode)-2),
,substr(postcode,1,2)||substr(postcode,3,length(po stcode)-2),
,substr(postcode,1,2)||substr(postcode,3,length(po stcode)-2),
,substr(postcode,1,2)||substr(postcode,3,length(po stcode)-2),
,substr(postcode,1,1)||lpad(to_number(substr(subst r(postcode,1,length(postcode)-3),2,2)),2,'0')||
,substr(postcode,1,1)||lpad(to_number(substr(subst r(postcode,1,length(postcode)-3),2,2)),2,'0')||
,substr(postcode,1,1)||lpad(to_number(substr(subst r(postcode,1,length(postcode)-3),2,2)),2,'0')||
,substr(postcode,1,2)||lpad(to_number(substr(subst r(postcode,1,length(postcode)-3),3,2)),2,'0')||
from temp order by 1;
Aug 10 '07 #1
0 1926

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

Similar topics

by: Lapchien | last post by:
I have a field for a postcode. I'd like another field to auto contain an 'area' number, based on the first part (only) of the postcode. For...
by: Fraser Dickson | last post by:
I am building a web based system using ASP.NET and VB.NET which has to interact with a web service which uses XML WDDX packets. I have been...
by: Alan | last post by:
Hi, I'm getting as bit confused with my queries. I simply (!) want to insert a space into a postcode field the 4th character from the right, so,...
by: miz_luvly | last post by:
hi guyz I have searched google and had no luck. I am trying to create a form where user enter postcode and address box fills up automatically...
by: bloc | last post by:
I am programming an interactive CV using xml, xslt and java script. The page consists of a header which contains links to various 'sections' on ...
by: warezguy05 | last post by:
Hi I'd like to forward users to a 'thank-you' page after they've submitted a form. I used this code and it worked perfectly till yesterday; ...
by: xhe | last post by:
I am now developing a website which needs Canadian PostCode Database. I can certainly buy one, but that will cost my hundered of $$, and my website...
by: DMcN | last post by:
Hi, I have a form set up in flash pointing to a PHP script on my webspace. I checked the PHP script and when viewed in a browser it returns the...
by: dominicowen | last post by:
Hi, I have a customer database in Access 2003 with customers postcodes. What I want to do is type in a postcode on access and it will link the...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.