UK postcode verification - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

UK postcode verification

Author

Topic

samtoffa
Yak Posting Veteran

60 Posts

Posted - 2004-07-07 : 06:48:09

I am writing a function to return a boolean value based on whether a string value passed to it is the correct format for a UK postcode (as defined in the UK Government Data Standards Catalogue - See below).

Format_________| Example Postcode
AN NAA_________| M1 1AA
ANN NAA________| M60 1NW
AAN NAA________| CR2 6XH
AANN NAA_______| DN55 1PT
ANA NAA________| W1A 1HQ
AANA NAA_______| EC1A 1BB

Has anyone already done this? I'm predicting that my attempt is likely to have far too many 'IF' statements. Someone MUST have a neat little bit of code that already does this!!
Sam

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-07-07 : 07:05:41

I read a topic on this in the past week....but i just can't find it...search here (or sqlservercentral) for Postcode....you might have more luck in tracking it down.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-07 : 07:24:36

[code]
CREATE TABLE UKPostCodeFormats (
format varchar(50) NOT NULL PRIMARY KEY
)

INSERT INTO UKPostCodeFormats
SELECT REPLACE(REPLACE(f, 'A', '[A-Z]'), 'N', '[0-9]')
FROM (
SELECT 'AN NAA' AS f
UNION ALL SELECT 'ANN NAA'
UNION ALL SELECT 'AAN NAA'
UNION ALL SELECT 'AANN NAA'
UNION ALL SELECT 'ANA NAA'
UNION ALL SELECT 'AANA NAA'
) AS A
[/code]
Then you do things like
[code]
SELECT DISTINCT postcode
FROM Addresses
WHERE NOT EXISTS (
SELECT *
FROM UKPostcodeFormats
WHERE postcode LIKE format
)
AND postcode IS NOT NULL
[/code]

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-07 : 07:31:42

Simple and elegant... Very Nice!

samtoffa
Yak Posting Veteran

60 Posts

Posted - 2004-07-07 : 07:34:12

Thanks guys!!
Arnold, this looks like just the sort of thing that I need, great!
I need to modify your code to take into account the following points that the government standard dictates
• The letters Q, V and X are not used in the first position.
• The letters I, J and Z are not used in the second position.
• The only letters to appear in the third position are A, B, C, D, E, F, G, H, J, K, S, T, U and W.
• The only letters to appear in the fourth position are A, B, E, H, M, N, P, R, V, W, X and Y.
• The second half of the Postcode is always consistent numeric, alpha, alpha format and the letters C, I, K, M, O and V are never used.

Any pointers???? Thanks in advance!!! Sam

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-07-07 : 08:29:41

constraints!!!

Kristen
Test

22859 Posts

Posted - 2004-07-07 : 11:37:11

Can you run a Regular Expression at the Client end, or some middleware?

If so:
http://regxlib.com/Search.aspx?k=uk%20postcode

Kristen

samtoffa
Yak Posting Veteran

60 Posts

Posted - 2004-07-08 : 06:58:50

Thanks all for your help!
Sam