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

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.

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
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


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!