Mr SQL's Blog

UK Postcodes can be difficult to handle in SQL, especially when trying to group results based on area code. Postcodes in the UK come in a variety of formats, the most common being AA9 9AA, A9 9AA, A99 9AA and AA99 9AA. Problems arise due to the variation in the total number of characters in a code, the different combinations of numbers and letters, and also the location of the space.

If you need to write a query to select the characters up to but excluding the space (e.g. AA9, A9, A99 or AA99), then the following approach can be used. It uses the LEFT command in conjunction with CHARINDEX to select the left side of the full code.

SELECT CompanyName, LEFT(Postcode, CHARINDEX(' ', Postcode) -1) AS 'Postcode'
FROM CompanyAddresses

However, if you need to select only the leading letters and not numbers (A or AA), then you can use a CASE statement:-

SELECT CompanyName, CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END AS 'Postcode'
FROM CompanyAddresses

Using either of the above two methods, you can successfully group results into Postcode areas. Use either method to group and select on Postcode.

SELECT CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END AS 'Postcode', COUNT(CompanyName) AS 'Companies in Postal Area'
FROM CompanyAddresses
GROUP BY CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END

Tags: CASE statement, CHARINDEX, ISNUMERIC, LEFT, Postcode, RIGHT, SQL, SQL Server, T-SQL