Avatar of PCCUtech
PCCUtech
Flag for Canada asked on

Parse out Province/state from address string

Hey all.

I have an address field that looks like this

<<cityname>> <<province/state>> <<postal/zip code>>

So a sample could look like this

Any Town SK A1A 1A1
Sometown NS B2B 2B2
Some other fun town CA 90210
Yourville ON C3C 3C3

From that I would need to extract
SK
NS
CA
ON

In essence I need to extract the Province or State Identifier.  They will all be 2 characters in length and be preceded and followed by a blank space so that is what I am looking for.

What I don't know is how I would go about doing that.  I guess I need a way to know where that substring starts so I can use SUBSTR to pull it out but for the life of me can't find a way to do that in Oracle.

Here's hoping one of you with more Oracle experience gets some easy points!

I should also mention that the tools I am using for the reporting only allow me to send a single select statement so it is not possible to use a function or stored procedure.

Thanks.
Oracle DatabaseSQL

Avatar of undefined
Last Comment
PCCUtech

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PCCUtech

ASKER
Fantastic ... I should have thought about looking up regular expressions ... simple and relatively elegant.

Testing the data looks like I have 2 addresses that aren't going to fit properly but they are both entered incorrectly so I am good with that!

Thanks so much!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23