0

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?

GullitsMullet
  • 348
  • 2
  • 8
  • 24
  • `REPLACE(postcode, ' ', '')` to get rid of spaces, then check length and so on. Share also what is correct postcode format – Lukasz Szozda Sep 11 '15 at 09:15
  • Are those UK postcodes? I'm not sure trying to validate them down in the database is a good idea. – Damien_The_Unbeliever Sep 11 '15 at 09:25
  • can't you grab a database/csv of valid post codes which you can to join and validate against? For example https://www.census.gov/econ/cbp/download/ – Kritner Sep 11 '15 at 10:08
  • I think we have a list of valid post codes I can use to check them but I would need to reformat my list first wouldn't I? e.g. I my list says 'M68HD' it won't match the valid code of 'M6 8HD' will it? – GullitsMullet Sep 11 '15 at 10:50
  • @GullitsMullet - do you think it's easier to find the correct place to insert spaces into arbitrary codes or to *remove* spaces from the official list and then just compare? – Damien_The_Unbeliever Sep 11 '15 at 10:56
  • I hadn't thought of doing it that way round, removing spaces from my list of valid postcodes is definitely going to be the easier way. Thanks – GullitsMullet Sep 11 '15 at 12:19
  • I stumbled across this old question while looking for something else. In a valid UK postcode the inward part (after the space) always has exactly three characters. So immediately before the last three characters is where the space belongs. Of course if, ignoring any space, you have fewer than 5 characters, then it wouldn't be worth inserting the space as you'd just be transforming an incomplete postcode into a "weird" format. – Steve Lovell Jul 15 '20 at 14:21

1 Answers1

2

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!

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448