Solved

Retreving records based on UK Postcode range

Posted on 2011-03-10
10
811 Views
Last Modified: 2012-05-11
Hello

I would like to return records based on a user specified postcode range, here is code I've got below which doesn't work.

DECLARE @FromPostCode nvarchar(4)
DECLARE @ToPostCode nvarchar(4)

SELECT Emails.Email, Customers.PostCode 
FROM Emails
LEFT JOIN ON
Customers.EmailAddress = Emails.Email

WHERE Customers.PostCode BETWEEN @FromPostCode AND @ToPostCode

Open in new window



Another problem is that some of the postcodes in the database aren't formatted correctly or even a valid post code so the query has to be build not to fail when it hits one of these.

For example :
SET @FromPostCode = EH1; 
SET @ToPostCode = EH11;

Open in new window


would return EH1,2,3,4,5,6,7,8,9,10,11

Thanks!
0
Comment
Question by:badpilot
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 35096008
You cant use between on a text string and expect it to understand that EH2 comes EH1 and EH11.

I am sure that someone has a method of doing it exactly somewhere but i cant see one.

My best guess would be to break the textual and numeric part into two fields giving you MUCH more flexibility
0
 

Author Comment

by:badpilot
ID: 35096096
Sorry, I just put that there as an example, I understand I can't use between, I have tried several methods of breaking it apart using substring or by using regex but keep hitting a wall with certain types of post code or because the data in the database is not correctly formatted.

For example:

WHERE
(len(postcode) = 6) AND (SUBSTRING(postcode, 1,3) LIKE EH[1-9]

Open in new window


This works but because of the different permutations of post code it only works on a specific type, it all becomes rather confusing.


I was hoping that someone would have already of came across this problem and had some sample code. Surely I'm not the first person who wants to search an sql database by a user specified post code range?
0
 
LVL 18

Expert Comment

by:deighton
ID: 35097543
use a function to zone it into the 3 parts

post town (always the first letters)

district (after the town, before the blank before the code)

code (the last 3 characters)


-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
ALTER  FUNCTION PCODE_BETWEEN_FORMAT
(
	-- Add the parameters for the function here
	@PCODE varchar(20)
)
RETURNS varchar(20)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @RETVAL VARCHAR(20)
	DECLARE @POSTTOWN VARCHAR(4);

	--first 1 or two characters is the postal town
	DECLARE @I int;
	SET @I = 0;
	DECLARE @DONE int;
	SET @DONE = 0;
	DECLARE @ATOM VARCHAR(1);
	SET @POSTTOWN = ' ';
	WHILE @I < = LEN(@PCODE) AND @DONE = 0
	BEGIN
		SET @I = @I + 1;
		SET @ATOM = UPPER(SUBSTRING(@PCODE, @I, 1));
		IF @ATOM >= 'A' AND @ATOM <= 'Z' 
			SET @POSTTOWN = @POSTTOWN + @ATOM
		ELSE
			IF @ATOM >= '0' AND @ATOM <= '9' 	
				SET @DONE = 1;
	END
	DECLARE @LASTPART varchar(3);
	DECLARE @MIDPART varchar(10);
	SET @LASTPART = RIGHT(RTRIM(@PCODE), 3);

	--now any other part is the district
	if @I + 2 < LEN(RTRIM(@PCODE))
		SET @MIDPART = SUBSTRING(@PCODE,@I, LEN(RTRIM(@PCODE)) - 2 - @I)
	ELSE 						
		SET @MIDPART = ' ';

	SET @RETVAL = left(LTRIM(@POSTTOWN) + '            ',5) + left(RTRIM(@MIDPART) + '          ',5) + RTRIM(@LASTPART);

	-- Return the result of the function
	RETURN @RETVAL

END
GO

Open in new window



then i did

select address05, dbo.PCODE_BETWEEN_FORMAT(address05) from isaddress order by dbo.PCODE_BETWEEN_FORMAT(address05)
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35097604
The only thing I could suggest is to create a function that creates a sortable version of the postcode.  
Your probllem is that a string sort puts correctly formatted postcodes N1, N11, N2 in that order whereas you need N1, N2, N11.    Having your function retuirn N01,N02, N11 as the sort code would give you this ability.

Encapsuating in a user defined function would make it much easier to test.
You could use the function either directly in your query or even store the sortable code on your underlying table.

If you have lots of badly formatted postcodes, then I guess your function would have to handle a lot of cases!
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35097625
The key idea is that the function to create the sortable code does not have to format the postcode to post office rules.    

However, using deightons approach to normalise the formatting to post office standards first would make the second function (to return a sortable code) easier to write.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 18

Expert Comment

by:lludden
ID: 35098396
I would hash the zip codes to a number in an indexed view or persisted calculated column (if there are lots of them) and use between on that.

Think of each digit as a base 36 digit and just calculate the decimal equivalent.
EE11 would equal 1+1*36+15*36^2+15*36^3 = 719317

That would allow 0-9 + A-Z as digits.

0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 35100412
The problem with Huddens approach is what happens when you want to search for all records with a postcode Town of EE?

As i said in the very first post, If you are only interested in the EE11 part of the postcode, i.e. the bit before the space, then just store the letters in one field and the rest in another.  I believe that would cover you for UK Post codes.

So EE11 would be stored in 2 fields pcLeters, a string and pcNumbers, an integer

Now you can search where pcString ="EE" and pcNumbers between 1 and 11
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35105615
Your best bet would be to save all the Postal codes in a table in the correct sort order and then filter on that order.
0
 
LVL 18

Accepted Solution

by:
deighton earned 500 total points
ID: 35107534
I looked at this post code validation one  time

I recall that the first LETTER or first two LETTERS are always the post town, and the last 3 characters are always NUMERAL-LETTER-LETTER, and are of course concerned with the the locality only.  The trickiest part is the postal district, that can be numerals or numerals or letters (but letters here are a rarity).  Full validation is tricky, because they keep introducing new codes!

My function above is not perfect, it cannot 'fix' any format blunder, but something like L257TX, it should get L   25    7TX, but it would not cope with L 257 TX  for example.

I think if spaces were compressed out via a while loop, it would fix that

how about - for you to CREATE or ALTER as Approriate

GO
/****** Object:  UserDefinedFunction [dbo].[PCODE_BETWEEN_FORMAT]    Script Date: 03/11/2011 10:50:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
ALTER  FUNCTION [dbo].[PCODE_BETWEEN_FORMAT]
(
	-- Add the parameters for the function here
	@PCODE varchar(20)
)
RETURNS varchar(20)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @RETVAL VARCHAR(20)
	DECLARE @POSTTOWN VARCHAR(4);
	DECLARE @PCODE_CLEAN varchar(20);
	SET @PCODE_CLEAN = '';

	DECLARE @I int;
	SET @I = 1;
	WHILE @I <= LEN(@PCODE)
	BEGIN
		--eliminate spaces
		IF LEN(RTRIM(SUBSTRING(@PCODE, @I, 1))) > 0 
			SET @PCODE_CLEAN = @PCODE_CLEAN + UPPER(SUBSTRING(@PCODE, @I, 1));
		SET @I = @I + 1;
	END

	--cursory validation
	DECLARE @INVALID int;
	SET @INVALID = 0;
	--has it got the minimum of characters
	IF LEN(@PCODE_CLEAN) < 5
		SET @INVALID = 1;			
	--has it got a postal town numeral of some description
	IF NOT(SUBSTRING(@PCODE_CLEAN,2,1) >= '0' AND SUBSTRING(@PCODE_CLEAN,2,1) <= '9'
		OR SUBSTRING(@PCODE_CLEAN,3,1) >= '0' AND SUBSTRING(@PCODE_CLEAN,3,1) <= '9')
		SET @INVALID = 1;			

	IF @INVALID = 1
		RETURN 'N/A';

	--first 1 or two characters is the postal town
	SET @I = 0;
	DECLARE @DONE int;
	SET @DONE = 0;
	DECLARE @ATOM VARCHAR(1);
	SET @POSTTOWN = ' ';
	WHILE @I < = LEN(@PCODE_CLEAN) AND @DONE = 0
	BEGIN
		SET @I = @I + 1;
		SET @ATOM = SUBSTRING(@PCODE_CLEAN, @I, 1);
		IF @ATOM >= 'A' AND @ATOM <= 'Z' 
			SET @POSTTOWN = @POSTTOWN + @ATOM
		ELSE
			IF @ATOM >= '0' AND @ATOM <= '9' 	
				SET @DONE = 1;
	END
	DECLARE @LASTPART varchar(3);
	DECLARE @MIDPART varchar(10);
	SET @LASTPART = RIGHT(RTRIM(@PCODE_CLEAN), 3);

	--now any other part is the district
	if @I + 2 < LEN(RTRIM(@PCODE_CLEAN))
		SET @MIDPART = SUBSTRING(@PCODE_CLEAN,@I, LEN(RTRIM(@PCODE_CLEAN)) - 2 - @I)
	ELSE 						
		SET @MIDPART = ' ';

	SET @RETVAL = left(LTRIM(@POSTTOWN) + '            ',5) + left(RTRIM(@MIDPART) + '          ',5) + RTRIM(@LASTPART);

	-- Return the result of the function
	RETURN @RETVAL

END

Open in new window



and to select between limits, this seems to work for me
note that your post code range has to be a valid post code format, you can't put in M0 to M99, although you could mod it with a flag to allow that

select address05, dbo.pcode_between_format(address05) from isaddress
 WHERE dbo.pcode_between_format(address05) BETWEEN dbo.pcode_between_format('M0 1AA') AND dbo.pcode_between_format('M99 1AA') ORDER BY dbo.pcode_between_format(address05)

Open in new window



if that works for you, then to speed up the work, you might want to look at using a trigger to call the function and update a field with the formatted code whenever a new record is entered or the post code is updated.  Then you can index your formatted field and search on it super quickly.
0
 

Author Closing Comment

by:badpilot
ID: 35108022
Thank you all for your very helpfull comments, in the end deighton provided me with working code out of the box. I'm very impressed Deighton, thank you!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now