troubleshooting Question

Parse out Province/state from address string

Avatar of PCCUtech
PCCUtechFlag for Canada asked on
Oracle DatabaseSQL
2 Comments1 Solution529 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros