CODEWITHSUNDEEP
sql-serverpostal-code
GullitsMullet
GullitsMullet

Reputation: 358

SQL Server validating postcodes

I have a table containing postcodes but there is no validation built in to the entry form so there is no consistency in the way they are stored in the database, sample below:

ID      Postcode
001742  B5
001745  
001746  
001748  DY3
001750  
001751  
001768  B276LL
001774  B339HY
001776  B339QY
001780  WR51DD

I want to use these postcode to map the distance from a central point but before I can do that I need to put them into a valid format and filter out any blanks or incomplete postcodes.

I had considered using

left(postcode,3) + ' ' + right(postcode,3)

To correct the formatting but this wouldn't work for postcodes like 'M6 8HD'

My aim is to get the list of postcodes in a valid format but I don't know how to account for different lengths of postcode. Is this there a way to do this in SQL Server?

Upvotes: 0

Views: 4106

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

As discussed in the comments, sometimes looking at a problem the other way around presents a far simpler solution.

You have a list of arbitrary input provided by users, which frequently doesn't contain the correct spacing. You also have a list of valid postcodes which are correctly spaced.

You're trying to solve the problem of finding the correct place to insert spaces into your arbitrary inputs to make them match the list of valid codes, and this is extremely difficult to do in practice.

However, performing the opposite task - removing the spaces from the valid postcodes - is remarkably easy to do. So that is what I'd suggest doing.

In our most recent round of data modelling, we have modelled addresses with two postcode columns - PostCode containing the postcode as provided from whatever sources, and PostCodeNoSpace, a computed column which strips whitespace characters from PostCode. We use the latter column for e.g. searches based on user input. You may want to do something similar with your list of Valid postcodes, if you're keeping it around permanently - so that you can perform easy matches/lookups and then translate those matches back into a version that has spaces - which is actually a solution to the original question posed!

Upvotes: 2

Related Questions