Formatting British Postcodes in SQL Server


This is just a quick knowledge share of how to format UK postal codes within a SQL Server database using a CASE statement. In this quick example, I have included some international postal codes to show that only the UK codes are affected.

I have created a new table, seen below, in a local database for use in this post.

08022017 blog image 1

Having inserted data into the table in various formats, the basic query for returning all UK postcodes in the recommended Post Office format across all data is as follows:


USE [Data]

      WHEN PostCode IS NULL 
          THEN '' 
      WHEN CHARINDEX(' ',PostCode,0) > 0 
          THEN UPPER(PostCode) 
      WHEN CHARINDEX(' ',PostCode,0) = 0 
        AND PostCode LIKE N'[A-Z][0-9][0-9][0-9][A-Z][A-Z]' 
          THEN ISNULL(LEFT(UPPER(PostCode), 3) + STUFF(RIGHT(UPPER(PostCode), 3), 1, 0, ' '), '')
      WHEN PostCode LIKE N'[A-Z][0-9][0-9][A-Z][A-Z]' 
          THEN ISNULL(LEFT(UPPER(PostCode), 2) + STUFF(RIGHT(UPPER(PostCode), 3), 1, 0, ' '), '')
      WHEN PostCode LIKE N'[A-Z][0-9][A-Z][0-9][A-Z][A-Z]' 
          THEN ISNULL(LEFT(UPPER(PostCode), 3) + STUFF(RIGHT(UPPER(PostCode), 3), 1, 0, ' '), '')
      WHEN PostCode LIKE N'[A-Z][A-Z][0-9][0-9][A-Z][A-Z]' 
          THEN ISNULL(LEFT(UPPER(PostCode), 3) + STUFF(RIGHT(UPPER(PostCode), 3), 1, 0, ' '), '')
      WHEN PostCode LIKE N'[A-Z][A-Z][0-9][0-9][0-9][A-Z][A-Z]' 
          THEN ISNULL(LEFT(UPPER(PostCode), 4) + STUFF(RIGHT(UPPER(PostCode), 3), 1, 0, ' '), '')
      WHEN PostCode LIKE N'[A-Z][A-Z][0-9][A-Z][0-9][A-Z][A-Z]' 
          THEN ISNULL(LEFT(UPPER(PostCode), 4) + STUFF(RIGHT(UPPER(PostCode), 3), 1, 0, ' '), '')
      WHEN PostCode NOT LIKE N'[A-Z][0-9][0-9][0-9][A-Z][A-Z]' 
        AND PostCode NOT LIKE N'[A-Z][0-9][0-9][A-Z][A-Z]' 
        AND PostCode NOT LIKE N'[A-Z][0-9][A-Z][0-9][A-Z][A-Z]' 
        AND PostCode NOT LIKE N'[A-Z][A-Z][0-9][0-9][A-Z][A-Z]' 
        AND PostCode NOT LIKE N'[A-Z][A-Z][0-9][0-9][0-9][A-Z][A-Z]' 
        AND PostCode NOT LIKE N'[A-Z][A-Z][0-9][A-Z][0-9][A-Z][A-Z]' 
          THEN UPPER(PostCode) 
      ELSE ' ' 
    END AS PostCode 


As I mentioned before, any postcode that does not resemble a standard UK format in any way, e.g. ‘N999’, will not be affected. However, any that do will be returned as one of the six standard UK formats.

08022017 blog image 3

In order to format for all international postal/ZIP codes, you can add further case statements into this script; be careful with this as many countries have similar formats, so being able to link this data with the rest of the customer’s address will be key. I would recommend using this as part of a stored procedure to clean the data as it passes through the data warehouse, dependent on your data model. 

Speak to an expert

Data platform consultant

Michael Montgomery

Contact Michael