Peter Larsson Blog

Patron Saint of Lost Yaks

Validate UK postcode

by Peter Larsson in  , , ,

CREATE FUNCTION dbo.fnValidatePostCodeUK
(
       @PostCode VARCHAR(8)
)
RETURNS BIT
AS
BEGIN
       RETURN CASE
                     --   Special case GIR 0AA
                     WHEN @PostCode LIKE 'GIR 0AA' THEN 1
                     -- Current postcode prefixes
                     WHEN   LEFT(@Postcode, 2) NOT IN ('AB', 'AL', 'BA', 'BB', 'BD', 'BH', 'BL', 'BN', 'BR', 'BS', 'BT', 'CA', 'CB', 'CF', 'CH', 'CM', 'CO', 'CR', 'CT', 'CV', 'CW', 'DA', 'DD', 'DE', 'DG', 'DH', 'DL', 'DN', 'DT', 'DY', 'EC', 'EH', 'EN', 'EX', 'FK', 'FY', 'GL', 'GU', 'GY', 'HA', 'HD', 'HG', 'HP', 'HR', 'HS', 'HU', 'HX', 'IG', 'IM', 'IP', 'IV', 'JE', 'KA', 'KT', 'KW', 'KY', 'L', 'LA', 'LD', 'LE', 'LL', 'LN', 'LS', 'LU', 'ME', 'MK', 'ML', 'NE', 'NG', 'NN', 'NP', 'NR', 'NW', 'OL', 'OX', 'PA', 'PE', 'PH', 'PL', 'PO', 'PR', 'RG', 'RH', 'RM', 'SA', 'SE', 'SG', 'SK', 'SL', 'SM', 'SN', 'SO', 'SP', 'SR', 'SS', 'ST', 'SW', 'SY', 'TA', 'TD', 'TF', 'TN', 'TQ', 'TR', 'TS', 'TW', 'UB', 'WA', 'WC', 'WD', 'WF', 'WN', 'WR', 'WS', 'WV', 'YO', 'ZE')
                           OR WHEN LEFT(@Postcode, 1) NOT IN ('B', 'E', 'G', 'L', 'N', 'S', 'W') THEN 0
                     -- AANN NAA
                     WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
                     -- AANA NAA
                     WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
                     -- ANN NAA
                     WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
                     -- AAN NAA
                     WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
                     -- ANA NAA
                     WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
                     --   AN NAA
                     WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
                     -- Not a valid postcode
                        ELSE 0
                END
END
Updated to reflect current valid postcodes only.

Legacy Comments
David le Quesne
2008-08-22		 re: Validate UK postcode
You can formulate your Regex for the LIKE in this format...

SELECT @bValid = 1 WHERE @strPostCode LIKE '[A-Za-z][A-Za-z][0-9][0-9][0-9][A-Za-z][A-Za-z]'
OR @strPostCode LIKE '[A-Za-z][A-Za-z][0-9][A-Za-z][0-9][A-Za-z][A-Za-z]'
OR @strPostCode LIKE '[A-Za-z][0-9][0-9][0-9][A-Za-z][A-Za-z]'
OR @strPostCode LIKE '[A-Za-z][A-Za-z][0-9][0-9][A-Za-z][A-Za-z]'
OR @strPostCode LIKE '[A-Za-z][0-9][0-9][A-Za-z][A-Za-z]'
OR @strPostCode LIKE '[A-Za-z][A-Za-z][0-9][0-9]'
OR @strPostCode LIKE '[A-Za-z][0-9][0-9]'
OR @strPostCode LIKE '[A-Za-z][A-Za-z][0-9]'
OR @strPostCode LIKE '[A-Za-z][0-9]'

RETURN @bValid
David le Quesne
2008-08-22		 re: Validate UK postcode
You can formulate your Regex for the LIKE in this format...

SELECT @bValid = 1 WHERE @strPostCode LIKE '[A-Za-z][A-Za-z][0-9][0-9][0-9][A-Za-z][A-Za-z]'
OR @strPostCode LIKE '[A-Za-z][A-Za-z][0-9][A-Za-z][0-9][A-Za-z][A-Za-z]'
OR @strPostCode LIKE '[A-Za-z][0-9][0-9][0-9][A-Za-z][A-Za-z]'
OR @strPostCode LIKE '[A-Za-z][A-Za-z][0-9][0-9][A-Za-z][A-Za-z]'
OR @strPostCode LIKE '[A-Za-z][0-9][0-9][A-Za-z][A-Za-z]'
OR @strPostCode LIKE '[A-Za-z][A-Za-z][0-9][0-9]'
OR @strPostCode LIKE '[A-Za-z][0-9][0-9]'
> OR @strPostCode LIKE '[A-Za-z][A-Za-z][0-9]'
OR @strPostCode LIKE '[A-Za-z][0-9]'

RETURN @bValid
Peso
2008-08-22		 re: Validate UK postcode
Really?

XX123YY will pass your first line of WHERE but it is not a VALID postcode.
The function is not only for validating the issue of having alpha and
numeric on the rigth spot, but also having the right kind of alpha and
numeric on the right spot.
Realityleak
2009-07-21		 re: Validate UK postcode
Peso you are a genius. I didn't realise until I looked more deeply that Q,V and X weren't allowed in the first position, I,J and Z weren't allowed int he 2nd position and the letters in the 3rd position were as you specified.

This is the best and most comprehensive postcode validator that I have seen. Kudos to you!

Thanks very much
Realityleak
2009-07-21		 re: Validate UK postcode
The only issues I can see in the validator is that you don't take into account manchester postcodes (left char is M and next 1 or 2 are numeric) and also, incorporating David's code, to make it more concise you could have done [0-9] as numeric fields.

fix for the manchester issue -

WHEN LEFT(@Postcode, 2) NOT IN ('AB', 'AL', 'BA', 'BB', 'BD', 'BH', 'BL', 'BN', 'BR', 'BS', 'BT', 'CA', 'CB', 'CF', 'CH', 'CM', 'CO', 'CR', 'CT', 'CV', 'CW', 'DA', 'DD', 'DE', 'DG', 'DH', 'DL', 'DN', 'DT', 'DY', 'EC', 'EH', 'EN', 'EX', 'FK', 'FY', 'GL', 'GU', 'GY', 'HA', 'HD', 'HG', 'HP', 'HR', 'HS', 'HU', 'HX', 'IG', 'IM', 'IP', 'IV', 'JE', 'KA', 'KT', 'KW', 'KY', 'L', 'LA', 'LD', 'LE', 'LL', 'LN', 'LS', 'LU', 'ME', 'MK', 'ML', 'NE', 'NG', 'NN', 'NP', 'NR', 'NW', 'OL', 'OX', 'PA', 'PE', 'PH', 'PL', 'PO', 'PR', 'RG', 'RH', 'RM', 'SA', 'SE', 'SG', 'SK', 'SL', 'SM', 'SN', 'SO', 'SP', 'SR', 'SS', 'ST', 'SW', 'SY', 'TA', 'TD', 'TF', 'TN', 'TQ', 'TR', 'TS', 'TW', 'UB', 'WA', 'WC', 'WD', 'WF', 'WN', 'WR', 'WS', 'WV', 'YO', 'ZE')
AND LEFT(@Postcode, 1) NOT IN ('B', 'E', 'G', 'L', 'N', 'S', 'W', 'M') THEN 0
Peso
2009-07-21		 re: Validate UK postcode
The fix is changing "OR" to "AND"?
UK POSTCODE
2012-08-07		 re: Validate UK postcode
asdsasda