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]

GO
SELECT 
    PostCode, 
    CASE 
      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 
FROM 
  [dim].[CustomerPostcode]

 

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

Latest from this author