Thinking outside the box

# 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

# 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

# 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.

# 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

# 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

# re: Validate UK postcode

The fix is changing "OR" to "AND"?

# re: Validate UK postcode

asdsasda