Using a range on Postcode in sql - Microsoft SQL Server

1 New Member

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','PE 5','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(up per(postcode),' B'),1 ,substr(postcod e,1,1)||lpad(to _number(substr( substr(postcode ,1,length(postc ode)-3),2,2)),2,'0') ||
substr(postcode ,length(postcod e)-3,length(postco de)),
decode(instr(up per(postcode),' G'),1
,substr(postcod e,1,1)||lpad(to _number(substr( substr(postcode ,1,length(postc ode)-3),2,2)),2,'0') ||
substr(postcode ,length(postcod e)-3,length(postco de)),
decode(instr(up per(postcode),' EC'),1
,substr(postcod e,1,2)||substr( postcode,3,leng th(postcode)-2),
decode(instr(up per(postcode),' NW'),1
,substr(postcod e,1,2)||substr( postcode,3,leng th(postcode)-2),
decode(instr(up per(postcode),' SW'),1
,substr(postcod e,1,2)||substr( postcode,3,leng th(postcode)-2),
decode(instr(up per(postcode),' SE'),1
,substr(postcod e,1,2)||substr( postcode,3,leng th(postcode)-2),
decode(instr(up per(postcode),' WC'),1
,substr(postcod e,1,2)||substr( postcode,3,leng th(postcode)-2),
decode(instr(up per(postcode),' L'),1
,substr(postcod e,1,1)||lpad(to _number(substr( substr(postcode ,1,length(postc ode)-3),2,2)),2,'0') ||
substr(postcode ,length(postcod e)-3,length(postco de)),
decode(instr(up per(postcode),' M'),1
,substr(postcod e,1,1)||lpad(to _number(substr( substr(postcode ,1,length(postc ode)-3),2,2)),2,'0') ||
substr(postcode ,length(postcod e)-3,length(postco de)),
decode(instr(up per(postcode),' S'),1
,substr(postcod e,1,1)||lpad(to _number(substr( substr(postcode ,1,length(postc ode)-3),2,2)),2,'0') ||
substr(postcode ,length(postcod e)-3,length(postco de))
,substr(postcod e,1,2)||lpad(to _number(substr( substr(postcode ,1,length(postc ode)-3),3,2)),2,'0') ||
substr(postcode ,length(postcod e)-3,length(postco de))))))))))))
from temp order by 1;

Aug 10 '07 #1

Subscribe Reply

2028

Similar topics

4118

postcode lookup

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 example, if a postcode LS4 4DJ was entered, another field would read the LS4 part and enter '7' in that new field. I have a table that contains an up to date list of just these first-part postcodes... Thanks, Lap

Microsoft Access / VBA

1903

Working with XML WDDX Packets using VB.Net

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 given the XML Packet Specification by the Web Service Provider but can't work out the best way to interact with the WDDX packets. Basically the way the Web Service works is that you send a WDDX formatted Request packet to the given URL, their

ASP.NET

6250

Insert a space into postcode

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, for example, ML201TQ becomes ML20 1TQ, which is the post offices format. Unfortunately I've been given a database with postcodes that don't have this space. Thanks

Microsoft Access / VBA

1848

autofill data using php/mysql

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 from the back-end db. can anyone guide me on how to do this. An example would be helpful or maybe few links to related tutorial. thanks in advance.

PHP

2387

Using javascript to capture the output of an anchor to alter a variable

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 the xml cv, a left and right menu, and a central panel. The central panel is intended to display the main content of the cv: when an anchor is selected from the header then the detail is supposed to appear. The javascript is supposed to select the appropriate section 'onclick' and output it....

Javascript

4149

Page redirection doesn't work using header function

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; header("Location: http://www.ernestoow.com/mudlands/bedankt.html"); Right now..after submitting the data, the form refreshes and is empty again..although the submitted data is inserted into the database and a confirmation email is sent to the submitter.

PHP

1926

Can anybody share me the Canadian PostCode Database

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 is only for education purpose, it won't make money. So I am afraid I can not affort it. Are there any good friends who can share me with you Canadian PostCode database, I need the longitude and latitude included in the DB. Thank you very very much in advance. If you still have other country's...

PHP

1736

Variables not being sent using send() or sendAndLoad()

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 parameters OK so I presume it is working. My problem is the variables captured by user input are not being sent to me. Please can anyone check my actionscript below for errors. I have tried send() and sendAndLoad() so I feel I may have an issue with my LoadVars.

Flash / Actionscript

2043

Postcode query in access to link with googlemaps

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 postcode on to googlemaps without having to type it in google map. So far I just got the command button which opens the query for me to type a postcode and it just links to google map but doesnt carry on the search for the postcode I entered. Hope this makes sense

Microsoft Access / VBA

9707

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10586

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10338

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10323

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

10082

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

5525

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4301

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3823

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2997

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General