Split UK Postcode with SQL Server function

Question

Post reply

Split UK Postcode with SQL Server function

C# Gnu

SSCertifiable

Points: 5973
More actions
June 28, 2012 at 9:57 am

#276983

I coded a function today to split a UK postcode into part one and two, even if the space is missing.
I just thought I would share with the world 🙂
It could be improved - by validating the the postcode is in the right format.
The function assumes the postcode will in the format descibed in this source:
url=http://www.list-logic.co.uk/marketing-advice/postcode-format-explained.html
Here is the code, and test examples..
CREATE FUNCTION dbo.fn_leanUtil_PostCodePart
(
@PostCode VARCHAR(15) ,
@Part TINYINT
)
-- WhoWhenWhat
-- Brigzy28 June 2012Initial
-- http:\\leansoftware.net
--
RETURNS VARCHAR(5)
AS
BEGIN
DECLARE @codepart VARCHAR(5)
-- Insert space if missing from postcode
IF CHARINDEX(' ', @postcode) =0
SELECT @postcode = SUBSTRING(@postcode, 0, LEN(@postcode) - 2) + ' '
+ SUBSTRING(@postcode, LEN(@postcode) - 2, LEN(@postcode))
IF @Part = 1
SELECT @codepart = LTRIM(SUBSTRING(@postcode, 1,
CHARINDEX(' ', @postcode)))
ELSE
SELECT @codepart = LTRIM(SUBSTRING(@postcode, CHARINDEX(' ', @postcode), 4))
RETURN @codepart
END
GO
DECLARE @postcode VARCHAR(15)
SET @postcode = 'LS176LP'
SELECT dbo.fn_leanUtil_PostCodePart(@postcode,1)
SELECT dbo.fn_leanUtil_PostCodePart(@postcode,2)
SET @postcode = 'LS82RG'
SELECT dbo.fn_leanUtil_PostCodePart(@postcode,1)
SELECT dbo.fn_leanUtil_PostCodePart(@postcode,2)
SET @postcode = 'LS8 2RG'
SELECT dbo.fn_leanUtil_PostCodePart(@postcode,1)
SELECT dbo.fn_leanUtil_PostCodePart(@postcode,2)
DROP FUNCTION fn_leanUtil_PostCodePart
go
C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

Did you add it to the scripts library?

http://www.sqlservercentral.com/Scripts/

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Question, is the 2nd part of the UK Postal code always the last 3 characters and the 1st part 3 or 4 characters long?

C# Gnu SSCertifiable Points: 5973 More actions · Answer 3

Robert Davis (6/28/2012)
Did you add it to the scripts library?
http://www.sqlservercentral.com/Scripts/

Thanks ..

Have sublitted now, status is 'Awaiting approval'..

Should really write a companion function 'fn_IsPostCodeValid' if not already done ...

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

C# Gnu SSCertifiable Points: 5973 More actions · Answer 4

Lynn Pettis (6/28/2012)
Question, is the 2nd part of the UK Postal code always the last 3 characters and the 1st part 3 or 4 characters long?

Yes Lynn that's what I have deduced from the post code link posted above - so was simple enough to just use right most 3 chars as part 2 and the rest is part 1 - minus any spaces.

Probably could be done in one line - but still ..

Now for postcode validate ... surely that must have been done though .. ?

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

C# Gnu (6/28/2012)
Lynn Pettis (6/28/2012)
Question, is the 2nd part of the UK Postal code always the last 3 characters and the 1st part 3 or 4 characters long?
Yes Lynn that's what I have deduced from the post code link posted above - so was simple enough to just use right most 3 chars as part 2 and the rest is part 1 - minus any spaces.
Probably could be done in one line - but still ..
Now for postcode validate ... surely that must have been done though .. ?

Okay, how about the following. You can use the hardcoded methos first, or you could use the inline table valued function in a cross apply to parse the postal codes in a table of addresses.

create function dbo.ParseUKPostalCode(

@pPostalCode varchar(8)

)

returns table

as return (select rtrim(left(@pPostalCode,len(@pPostalCode) - 3)) as Outward, right(@pPostalCode,3) as Inward);

GO

DECLARE @postcode VARCHAR(15);

SET @postcode = 'LS176LP'

select rtrim(left(@postcode,len(@postcode) - 3)), right(@postcode,3);

select * from dbo.ParseUKPostalCode(@postcode);

SET @postcode = 'LS82RG'

select rtrim(left(@postcode,len(@postcode) - 3)), right(@postcode,3);

select * from dbo.ParseUKPostalCode(@postcode);

SET @postcode = 'LS8 2RG'

select rtrim(left(@postcode,len(@postcode) - 3)), right(@postcode,3);

select * from dbo.ParseUKPostalCode(@postcode);

go

DROP FUNCTION dbo.ParseUKPostalCode;

go

C# Gnu SSCertifiable Points: 5973 More actions · Answer 6

Yep Lynn that looks sound.

As regards postcode validating I found an intersting post - essential reading on the subject!

It turns out there are some exceptions to the rule..:ermm:

Quote source =http://www.hexcentral.com/articles/sql-postcodes.htm

"Checking outward codes against a lookup table

I mentioned earlier that the only way to validate a postcode against a lookup table is to use the PAF, which is expensive. That's true of the code as a whole. But it's much more feasible to just check the outward portion of the code. There are around 3,000 outward codes currently in use - compared to more than 27 million full postcodes.

What's more, it's possible to download a list of outward codes. One place where you can do that is the EasyPeasy site, where the list is available free of charge. It comes in the form of a comma-delimited file (handy for opening in Excel) and as a SQL script which generates a table of the codes. Unfortunately for SQL Server folk, the script is in MySQL syntax, but it can be adapted to T-SQL without too much effort.

One problem with the EasyPeasy file is that it doesn't include the 200 or so non-geographic outward postcodes. These are special codes that have been allocated to very large organisations. They conform to the same syntax rules as other codes, but are independent of any particular geographic area. They include, for example, the VAT Central Unit at BX5, and British Gas at GU95.

Because there are relatively few of these non-geographic codes, it would be possible to add them to the lookup table manually. You can find an up-to-date list here."

-- End Quote

I can see that we code something to validate postcode format taking into account the weird ones mentioned - but you could have a postcode that’s theoretically valid but that doesn’t actually correspond to a real address. Getting hold of an entire list of existing postcodes looks like something one has to pay for in the UK..

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

I wonder why you would want to split a postcode?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

C# Gnu SSCertifiable Points: 5973 More actions · Answer 8

mister.magoo (6/28/2012)
I wonder why you would want to split a postcode?

Very good question!

The system uses Oyez forms software.

It is a type of form filling software - used by the legal sector and other officials in the UK.

The form software allows you to place an SQL query next to each data item on the form - so you can pre-fill the form with your local data.

For some reason Oyez have two separate boxes for post code - first part and second part.

Personally I think it is wrong that they do so - particularly now that I know that there are some weird postcodes that do not fit this model!

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

C# Gnu (6/28/2012)
Yep Lynn that looks sound.
As regards postcode validating I found an intersting post - essential reading on the subject!
It turns out there are some exceptions to the rule..:ermm:
Quote source =http://www.hexcentral.com/articles/sql-postcodes.htm
"Checking outward codes against a lookup table
I mentioned earlier that the only way to validate a postcode against a lookup table is to use the PAF, which is expensive. That's true of the code as a whole. But it's much more feasible to just check the outward portion of the code. There are around 3,000 outward codes currently in use - compared to more than 27 million full postcodes.
What's more, it's possible to download a list of outward codes. One place where you can do that is the EasyPeasy site, where the list is available free of charge. It comes in the form of a comma-delimited file (handy for opening in Excel) and as a SQL script which generates a table of the codes. Unfortunately for SQL Server folk, the script is in MySQL syntax, but it can be adapted to T-SQL without too much effort.
One problem with the EasyPeasy file is that it doesn't include the 200 or so non-geographic outward postcodes. These are special codes that have been allocated to very large organisations. They conform to the same syntax rules as other codes, but are independent of any particular geographic area. They include, for example, the VAT Central Unit at BX5, and British Gas at GU95.
Because there are relatively few of these non-geographic codes, it would be possible to add them to the lookup table manually. You can find an up-to-date list here."
-- End Quote
I can see that we code something to validate postcode format taking into account the weird ones mentioned - but you could have a postcode that’s theoretically valid but that doesn’t actually correspond to a real address. Getting hold of an entire list of existing postcodes looks like something one has to pay for in the UK..

Instead of all the trimming and LEN stuff, just use a single replace to replace all spaces with nothing. Then it's a simple matter of LEFt(3) and RIGHT (3).

I'd also recommend doing some pre-validation on the GUI side to at least check to see if the alpha characters are alpha and the numeric characters are numeric.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Jeff Moden (6/28/2012)
C# Gnu (6/28/2012)
Yep Lynn that looks sound.
As regards postcode validating I found an intersting post - essential reading on the subject!
It turns out there are some exceptions to the rule..:ermm:
Quote source =http://www.hexcentral.com/articles/sql-postcodes.htm
"Checking outward codes against a lookup table
I mentioned earlier that the only way to validate a postcode against a lookup table is to use the PAF, which is expensive. That's true of the code as a whole. But it's much more feasible to just check the outward portion of the code. There are around 3,000 outward codes currently in use - compared to more than 27 million full postcodes.
What's more, it's possible to download a list of outward codes. One place where you can do that is the EasyPeasy site, where the list is available free of charge. It comes in the form of a comma-delimited file (handy for opening in Excel) and as a SQL script which generates a table of the codes. Unfortunately for SQL Server folk, the script is in MySQL syntax, but it can be adapted to T-SQL without too much effort.
One problem with the EasyPeasy file is that it doesn't include the 200 or so non-geographic outward postcodes. These are special codes that have been allocated to very large organisations. They conform to the same syntax rules as other codes, but are independent of any particular geographic area. They include, for example, the VAT Central Unit at BX5, and British Gas at GU95.
Because there are relatively few of these non-geographic codes, it would be possible to add them to the lookup table manually. You can find an up-to-date list here."
-- End Quote
I can see that we code something to validate postcode format taking into account the weird ones mentioned - but you could have a postcode that’s theoretically valid but that doesn’t actually correspond to a real address. Getting hold of an entire list of existing postcodes looks like something one has to pay for in the UK..
Instead of all the trimming and LEN stuff, just use a single replace to replace all spaces with nothing. Then it's a simple matter of LEFt(3) and RIGHT (3).
I'd also recommend doing some pre-validation on the GUI side to at least check to see if the alpha characters are alpha and the numeric characters are numeric.

Like this?

select left(replace(@postcode,' ',''),len(replace(@postcode,' ','')) - 3), right(@postcode,3);

That is what I had first, then decided to go with fewer function calls as the outward portion of the UK Postal Code can be 2 to 4 characters.

subbu1 Hall of Fame Points: 3281 More actions · Answer 11

pls try below code.

CREATE FUNCTION dbo.fn_leanUtil_PostCodePart

(

@PostCode VARCHAR(15) ,

@Part TINYINT

)

-- WhoWhenWhat

-- Brigzy28 June 2012Initial

-- http:\\leansoftware.net

--

RETURNS VARCHAR(5)

AS

BEGIN

DECLARE @codepart VARCHAR(5)

-- Insert space if missing from postcode

IF CHARINDEX(' ', @postcode) =0

SELECT @postcode = SUBSTRING(@postcode, 0, LEN(@postcode) - 2) + ' '

+ SUBSTRING(@postcode, LEN(@postcode) - 2, LEN(@postcode))

IF @Part = 1

SELECT @codepart = SUBSTRING(@postcode, 1,

CHARINDEX(' ', @postcode))

ELSE

SELECT @codepart = SUBSTRING(@postcode, CHARINDEX(' ', @postcode), 4)

RETURN @codepart

END

GO

DECLARE @postcode VARCHAR(15)

SET @postcode = 'LS176LP'

SELECT dbo.fn_leanUtil_PostCodePart(@postcode,1)

SELECT dbo.fn_leanUtil_PostCodePart(@postcode,2)

SET @postcode = 'LS82RG'

SELECT dbo.fn_leanUtil_PostCodePart(@postcode,1)

SELECT dbo.fn_leanUtil_PostCodePart(@postcode,2)

SET @postcode = 'LS8 2RG'

SELECT dbo.fn_leanUtil_PostCodePart(@postcode,1)

SELECT dbo.fn_leanUtil_PostCodePart(@postcode,2)

DROP FUNCTION fn_leanUtil_PostCodePart

go

C# Gnu SSCertifiable Points: 5973 More actions · Answer 12

Ok, pause for one second - here is the scenario in more detail:

The GUI does not do any validation check on the postcode.

The GUI is a 3rd party and cannot be modified

The postcode may or may not contain a space.

The format 'should' match these patterns:

Source http://www.hexcentral.com/articles/sql-postcodes.htm
Pattern Example
A9 9AA L2 3SW
A99 9AA M16 0RA
AA9 9AA NW3 2RR
AA99 9AA EH12 9DN
A9A 9AA W1A 1HQ
AA9A 9AA SW1A 2AA
....
Validating postcodes in T-SQL
Given a postcode column within a SQL Server database, here's a T-SQL query that will check the validity of the codes, based on the syntax rules discussed above. The query returns all invalid postcodes from the relevant table.
-- Returns invalid postcodes
SELECT postcode FROM Addresses
WHERE
patindex('[A-Z][0-9] [0-9][A-Z][A-Z]', postcode) = 0 and
patindex('[A-Z][0-9][0-9] [0-9][A-Z][A-Z]', postcode) = 0 and
patindex('[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]', postcode) = 0 and
patindex('[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]', postcode) = 0 and
patindex('[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', postcode) = 0 and
patindex('[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', postcode) = 0

But there are oddities

Source = http://en.wikipedia.org/wiki/Postcodes_in_the_United_Kingdom
Non-geographic codes
Most postcodes map directly to a geographic area but some are used only for routing and cannot be used for navigation or distance-finding applications.Non-geographic postcodes are often used for direct marketing and PO boxes. Some postcode sectors or districts are set aside solely for non-geographic postcodes, including EC50, BS98, BT58, IM99, N1P, NE99, SW99 and JE4.
Girobank's headquarters in Bootle uses the non-geographic postcode GIR 0AA which is unique in format. There is also a non-geographic postcode area, BX, solely for non-geographic addresses. Postcodes beginning with BX follow the standard format but are allocated independently of the location of the recipient and can be retained in the event of the recipient moving. Prominent users include Lloyds TSB[33] and HM Revenue and Customs.[34] There is a special postcode for letters to Father Christmas: SAN TA1.
Special postcodes
This section may contain original research. Please improve it by verifying the claims made and adding references. Statements consisting only of original research may be removed. (May 2012)
Organisations that receive enough post to justify having a dedicated postcode also, in a small proportion of cases, have their organisation name reflected in the last part of the code. Prominent examples include:
BS98 1TL
TV Licensing[37]
BX1 1LT
Lloyds TSB Bank (non-geographic address)[38]

So a bit more thought required to get this right.

and not forgetting 'SAN TA1'. 🙂

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015