INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Get start of post code

Get start of post code

(OP)
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(dbo.OrderHeader.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

RE: Get start of post code

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.

RE: Get start of post code

(OP)
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

RE: Get start of post code

Would this work?

LEFT(RIGHT(dbo.OrderHeader.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

RE: Get start of post code

(OP)
Thanks gmmastros

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

RE: Get start of post code

Is that post code always at that position, really?

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

Bye, Olaf.

RE: Get start of post code

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):

SUBSTRING(dbo.OrderHeader.DeliveryAddress, - 7, 2) AS PC

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Get start of post code

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 thumbsdown

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close