UK Post code Area

Question

UK Post code Area

c.simpson

Mr or Mrs. 500

Points: 593
More actions
January 26, 2015 at 4:23 am

#104250

I would like to extract the first part of the post code.
for example I have
AA1 2BB
A1 2B
A4 2BB
I would like my field to ready
AA
A
A
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

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

Neil Burton SSC-Insane Points: 22999 More actions · Answer 1

c.simpson (1/26/2015)
I would like to extract the first part of the post code.
for example I have
AA1 2BB
A1 2B
A4 2BB
I would like my field to ready
AA
A
A
Thanks

left(Postcode,patindex('%[0-9]%',postcode)-1)

Does that do what you need?

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

c.simpson Mr or Mrs. 500 Points: 593 More actions · Answer 2

Thanks for the quick reply

When I try your code I get

Invalid length parameter passed to the LEFT or SUBSTRING function.

as a error

Thanks

Neil Burton SSC-Insane Points: 22999 More actions · Answer 3

Have you got spaces in all your postcodes?

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

c.simpson Mr or Mrs. 500 Points: 593 More actions · Answer 4

Yes, our post code are like AA1 1SS, or B1 1SS

I just need the AA or B part of the code

Thanks

Neil Burton SSC-Insane Points: 22999 More actions · Answer 5

Have all your postcodes definitely got numbers in them? I know they should have but it might be worth checking.

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

c.simpson Mr or Mrs. 500 Points: 593 More actions · Answer 6

Just check the data, you are right some don't have numbers.

Thanks for your help,

Is there a way around the error is the field doesn't have any number?

Neil Burton SSC-Insane Points: 22999 More actions · Answer 7

SELECT

left(Postcode,patindex('%[0-9]%',postcode)-1)

FROM YourTable

where patindex('%[0-9]%',postcode) > 0

order by 3

That should do the trick for all the postcodes with numbers in them.

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

c.simpson Mr or Mrs. 500 Points: 593 More actions · Answer 8

c.simpson

Mr or Mrs. 500

Points: 593

January 26, 2015 at 5:31 am

#1773056

Thanks for your help

Neil Burton SSC-Insane Points: 22999 More actions · Answer 9

You're welcome. I've done some tinkering with postcodes myself in the past so I recognised what you were trying to do 🙂

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

c.simpson Mr or Mrs. 500 Points: 593 More actions · Answer 10

Something has just cropped up.

The statement works as it should and exports the post code area. However my historical data is not that good. I would like to include all my data as it is, which included just letters or even a decimal place. This is due to a poor set of data which was imported from a old system.

Is it possible to have a select statement which pull out the area letters, no matter what is in the field. so if the record has a decimal place, it would return either a decimal place or nothing.

Thanks for your help.

David Burrows SSC Guru Points: 65142 More actions · Answer 11

LEFT(Postcode,PATINDEX('%[^A-Za-z]%',Postcode+'0')-1)

Far away is close at hand in the images of elsewhere.
Anon.

c.simpson Mr or Mrs. 500 Points: 593 More actions · Answer 12

c.simpson

Mr or Mrs. 500

Points: 593

January 26, 2015 at 8:10 am

#1773126

excellent, thanks for your help

P Jones SSChampion Points: 12361 More actions · Answer 13

I found this guidance many years ago but can't find a link so here's the text:

Guidance on recording valid postcodes

The postcode is a combination of between five and seven letters/numbers which define four different levels of geographic unit. It is part of a coding system created and used by the Royal Mail across the United Kingdom for sorting mail. The postcodes are an abbreviated form of address, which enable a group of delivery points (a delivery point being a property or a post box) to be specifically identified.

Each postcode consists of two parts. The first part is the outward postcode, or outcode. This is separated by a single space from the second part, which is the inward postcode, or incode.

The outward postcode enables mail to be sent to the correct local area for delivery. This part of the code contains the area and the district to which the mail is to be delivered.

The inward postcode is used to sort the mail at the local area delivery office. It consists of a numeric character followed by two alphabetic characters. The numeric character identifies the sector within the postal district. The alphabetic characters then define one or more properties within the sector.

The following is a list of the valid formats of postcode. An ‘A’ indicates an alphabetic character, an ‘N’ indicates a numeric character.

FormatExample

Outcode IncodePostcode

AN NAA M1?1AA??

ANN NAA M60?1NW?

AAN NAA CR2?6XH?

AANN NAA DN55?1PT

ANA NAA W1P?1HQ?

AANA NAA EC1A?1BB

(Where ? represents a space)

The following characters are never used in the inward part of the postcode:

C I K M O V

-------------------------------------------------------------------------

So to get the first part, replace all spaces with '' (empty string) and remove the last three characters. That takes into account badly formatted codes without the space or incorrectly spaced.

There are various regular expressions available online to validate UK postcodes based on the rules above.

BrainDonor SSCoach Points: 19254 More actions · Answer 14

If you need to test for any interesting postcodes: http://www.softwaretestingclub.com/forum/topics/fun-postcodes-to-use-when-testing?id=751045%3ATopic%3A164159&page=1#comments

Steve Hall
Linkedin
Blog Site