[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Retreving records based on UK Postcode range

Posted on 2011-03-10
10
Medium Priority
?
876 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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
 
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 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

649 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