Validating UK Postcodes in SQL Server

While working with a central government client recently I was asked if I had a method of validating postcodes stored in a SQL Server table to check if they conformed to standard naming conventions.
Having checked with various departmental source I could not find a departmental standard method so started to do some general research expecting to quickly find a de facto standard, only to be disappointed when one could not be found. I found reference to a number of different rule sets that had slight variations.
Checking with a few members or the department they pointed me to this reference document, which is what they use for their standards. The details are therefore what I based my processes on.
The raw data I was dealing with had the postcode supplied and initially loaded in two parts. each was stripped of leading or training spaces before being joined together with a space between. I there did not have to take into consideration strange spacing I was confident that the post codes would at least have a single space separating the two halves.
This first example is a basic query that will return all the post codes in the table or view a 0 for valid or 1 for invalid postcodes with a line being returned for each row in the table.
USE [database]
GO

SELECT [Row_key]
,[POSTCODE_P1]
,[POSTCODE_P2]  
,[POSTCODE]
,CASE
WHEN [POSTCODE] IS NULL
  THEN '0'
-- A9 9AA
WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]'
THEN '0'
-- A99 9AA
WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]'
THEN '0'
  -- AA9 9AA
  WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]'
     THEN '0'
  -- AA99 9AA
  WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]'
     THEN '0'
  -- A9A 9AA
  WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]'
     THEN '0'
  -- AA9A 9AA
  WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABCDEFGHKLMNOPQRSTUVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]
  THEN '0'
  ELSE '1'
  END AS [POSTCODE_VALIDATE]
FROM [database].[schema].[table_or_view]
GO
This second example is builds upon the first query and only returns rows that have failed the validation.
USE [database]
GO
SELECT * FROM
(SELECT [Row_key]
,[POSTCODE_P1]
,[POSTCODE_P2]
,[POSTCODE]
,CASE
WHEN [POSTCODE] IS NULL
THEN '0'
-- A9 9AA
WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]'
THEN '0'
-- A99 9AA
WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]'
THEN '0'
-- AA9 9AA
WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]'
THEN '0'
-- AA99 9AA
WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]'
THEN '0'
-- A9A 9AA
WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]'
THEN '0'
-- AA9A 9AA
WHEN [POSTCODE] LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABCDEFGHKLMNOPQRSTUVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]'
THEN '0'
ELSE '1'
END AS [POSTCODE_VALIDATE]
FROM [database].[schema].[table_or_view]
)AS SQ
WHERE [POSTCODE_VALIDATE] = 1
GO
The result set will include the row key info and the postcode info for postcodes that fail validation.

About the author