Re: select separate values in a string: sql

Alex Doster wrote:
> Pardon me if this is the wrong group to ask this question to.
>
> I have a character field that contains address information
> (don't ask why I didn't seperate it)
> and I need to use pl/sql to grab individual parts of the string.
>
> Example - Field C_Address contains
>
> "1234 W. Value Street Denver, CO 99508"
>
> The query should return
> STREET 1234 W. Value Street
> CITY Denver
> STATE Colorado
> ZIPCODE 99508
>
>
> The only thing I can think of to get the zipcode is to use the right()
> function.
> SELECT RIGHT("C_ADDRESS", 5) AS ZIPCODE
> But am not sure if this will even work.
> Other than that, I'm stumped.
>
> Thanks for help
> Lex

Can you guarantee that addresses will always be in this format? If so, then simple substr() and instr() commands will do the trick. However, if any of the following might appear in the address line, you will need a "smarter" parser:

1. Zip code can be 5 or 9 characters (99508 or 99508-1234). Other countries do not use zip code (eg, Canada uses postal code).
2. Can state ever be spelled out? Do you have addresses from foreign countries?
3. Cities can contain punctuation as well (eg, Ft. Lauderdale). If you followed US Post Office recommendations, you would not have *any* punctuation - no commas, no periods, and all uppercase.
4. If it's a business address, where do you find (in the database) the Suite number? If an apartment, where do you find the number? Some older cities have duplexes with addresses like 1234 1/2 Some St.
5. Some rural routes have only route number and box number (or one of them) - eg, RR4 Box 13. Speaking of box numbers, how do you store post office boxes?

This list is not all-inclusive ... but you generally can't get away with a simple parser to pick apart pieces of addresses. Back in my COBOL days, the USPS supplied a subroutine that would parse most US addresses. However, not even that was 100% accurate.

Good luck. Received on Tue Nov 19 2002 - 15:58:15 CST