Link to home
Start Free TrialLog in
Avatar of PCCUtech
PCCUtechFlag 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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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!