Re: ORACLE SUBSTR FUNCTION HELP

On 29 Sep 2002 07:11:41 -0700, dchou1108_at_hotmail.com (Dave) wrote:

>I am trying to get the city name from an address column of type VARCHAR2(80)
>
>The address looks as follow:
>ADDRESS
>--------------------------------------------------------------------------------
>2800 Montrose Ave. Lacresenta, CA 92832
>1231 Fullerton Ave. Fullerton, CA 92898
>3301 Huston Ave. Garden Grove, CA 92123
>1234 Placentia Rd. Placentia, CA 92871
>34 Starbucks Rd. Seattle, WA 43249
>1214 Ford Ave. LA, CA 98649
>1317 Bond Ave. LA, CA 98649
>
>How do I get the city for the substring. I am trying to group my query by the city.
>
>Thanks

You have just demonstrated it was a design error to have the city in the same column as the street address. Obviously there are going to be no rules at all to 'parse' the city out. (Someone might forget the , before the state abbreviation, or forget the state abbreviation at all), so I would strongly advise you to rectify to the design error, or to use a meaningful combination of substr and instr, which I leave you as an exercise, as this is a homework question anyway.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Sep 29 2002 - 10:20:40 CDT