Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Validate UK postcode

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.

Print | posted on Wednesday, August 13, 2008 1:46 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# 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
8/22/2008 9:55 AM | David le Quesne
Gravatar

# 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
8/22/2008 2:34 PM | David le Quesne
Gravatar

# 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.
8/22/2008 2:45 PM | Peso
Gravatar

# 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
7/21/2009 12:02 PM | Realityleak
Gravatar

# 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
7/21/2009 12:08 PM | Realityleak
Gravatar

# re: Validate UK postcode

The fix is changing "OR" to "AND"?
7/21/2009 3:43 PM | Peso
Gravatar

# re: Validate UK postcode

asdsasda
8/7/2012 8:34 AM | UK POSTCODE
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET