Get start of post code 1

[Cpreston]

HI

We have a delivery address field which is one long text field. For example we could have

York Road Leeds LS1 1BB


I want to be able to extract the LS part of the post code

I currently have a view and have this small bit of code as

RIGHT(dbrderHeader.DeliveryAddress, 8) AS PC

This brings back the whole postcode as the result LS1 1BB. I cannot find a way so it gets the LS only. Any ideas anyone please.

THanks

 

[Olaf Doschke]

RIGHT is a function giving - you guess it - the right part of a string (in the sense of the direction, opposite of left, not opposite of wrong)

So your small bit of code extracts the last 8 characters always.

PATINDEX should be used with a search pattern of '% LS%', which ensures to find a LS after a space. After knowing that position determine the first space after that position and you have the two positions needed to compute length and feed SUBSTRING with first position and length to extract.

Bye, Olaf.

 

[Cpreston]

Hi

Sorry never used Patindex and not sure it would work. The post code would not always be LS and could be any letters in the UK.
Also I am not brilliant on coding. I have just googled Patindex and I cant find a simple example to try.

Could you or someone point me in the right direction

Thanks

 

[gmmastros]

Would this work?

[!]LEFT([/!]RIGHT(dbrderHeader.DeliveryAddress, 8)[!],2)[/!] AS PC


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Thanks gmmastros

Works fine and simple to implement. Thanks for all the replys

 

[Olaf Doschke]

Is that post code always at that position, really?

Also this could be SUBSTRING(dbrderHeader.DeliveryAddress,8,2)

Bye, Olaf.

 

[Andrzejek]

Since the 8 characters from the end is also grabbing a space before LS1 (based on example provided), Olaf's syntax should be (I guess):
[tt]
SUBSTRING(dbrderHeader.DeliveryAddress, - 7, 2) AS PC
[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[softhemc]

Just to throw a spanner in the works:-

UK postcodes do not need a space between the two parts and as far as I am aware, with one exception (Girobank) have the following format:


AN NAA
ANN NAA
AAN NAA
AANN NAA
ANA NAA
AANA NAA


where A is a letter and N is a number.

The second part is always Number, Letter, Letter.

Therefore (since the space is optional), the length can vary between 5 characters and 8 characters.

Certain letters in certain positions are also not allowed, and some are reserved for future use.

Many years ago I had to write a program to interact with the Postoffice Address File - which was great fun