Remove String from column Post Code Field

Question

Remove String from column Post Code Field

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

andrew gothard SSChampion Points: 12301 More actions · Answer 1

BWFC (9/8/2015)
A correctly formed postcode can be 7 or 8 characters - eg SW1A 4WW so change RIGHT(PostalCode, 7) to RIGHT(PostalCode, 8) and LTRIM the result
Postcodes can be 6,7 or 8 characters, M1 2DF is also a valid format.

That's a new one on me - cheers for that.

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Neil Burton SSC-Insane Points: 22923 More actions · Answer 2

andrew gothard (9/9/2015)
BWFC (9/8/2015)
A correctly formed postcode can be 7 or 8 characters - eg SW1A 4WW so change RIGHT(PostalCode, 7) to RIGHT(PostalCode, 8) and LTRIM the result
Postcodes can be 6,7 or 8 characters, M1 2DF is also a valid format.
That's a new one on me - cheers for that.

You're welcome. It's mainly big cities that are single letters. Manchester's M, Birmingham's B, Liverpool's L and Glasgow's G. N,E and W are parts of London with 6 character postcodes too.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 3

A UK postcode is always formed of two parts

The first part and will be of the form 1 or 2 letters indicating the posttown followed by 1 or 2 numbers this is followed by a single space then 1 number and two letters.

Note some areas of London use a non standard variant where the second letter of the first part can be a number eg. SW1E 2AA and also Girobank in Bootle also has a non standard format GIR 0AA - this is historic because that postcode was created before the formatting standard was laid down.

There are a few other exceptions e.g. SAN TA - I don't think you will be surprised as to what that is intended for.

Pack_Star Mr or Mrs. 500 Points: 547 More actions · Answer 4

Hi All,

Thank you for the additional information. I am still trying find a fix regarding what has been variable length of the postcode string. I would welcome any further suggestions.

Thanks in advance

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 5

Assume whoever input the postcode does not understand where the space should be - you probably wont be far off.

Strip out all spaces then take last 3 chars, prepend with space, prepend with the rest (2-4 chars).

Pack_Star Mr or Mrs. 500 Points: 547 More actions · Answer 6

Hi All,

After taking into consideration (e.g. variants & possible typos) it will be very difficult to provide a future proof solution. It looks like the best approach will be to cleanse the table or use an alternative view as a lookup.

Many thanks for your suggestions and advice.

Kind regards

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 7

We have postcodes in our database which only have the first "half", I expect that is not uncommon - i.e. the user was able to figure out / was given the town-bit only. That is still (so I believe?) useful to post Office / Postie so I think worth preserving if you have it.

Thus it might be worth also trying to handle "Northants NN4"

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 8

Dealing with partial postcodes greatly complicates things

Is B91 really B91 or is it supposed to be B9 1

B91 is Solihull and B9 is Saltley about 10 miles apart and much further in terms of affluence.

Errors arising from miscoding these may greatly change the results of the analysis. As a consequence you may wish to exclude any incomplete entries in some cases.

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 9

crmitchell (9/14/2015)
Dealing with partial postcodes greatly complicates things.

Indeed ... ultimately its should be down to whatever the Client wants ...

"Is B91 really B91 or is it supposed to be B9 1. B91 is Solihull and B9 is Saltley about 10 miles apart and much further in terms of affluence."

The "9" might have got there from a badly written "A" incorrectly transcribed ... Postie's got quite a bit further to go in that case!

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 10

Just a short walk from Bradford lol.

I suppose it could also be a badly written S - i.e. Bristol - shouldn't take them more than a few minutes. 😀

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 11

🙂 Your point about Affluence is important though. I tend to think in terms of "getting goods delivered" but no doubt our clients are also reporting using geo-coding for other purposes.