Re: ORACLE SUBSTR FUNCTION HELP

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 29 Sep 2002 12:30:24 -0700
Message-ID: <92eeeff0.0209291130.4cdfe1eb@posting.google.com>

dchou1108_at_hotmail.com (Dave) wrote in message news:<8f19196e.0209290611.38cc9b2_at_posting.google.com>...
> 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

It would be easy to give a code example but maybe you should try to do it yourself with a hint.

Use combination of SUBSTR and INSTR. Assuming address is always in this format then use INSTR to look for '.' and then ','. Then use SUBSTR to extract whatever is between '.' and ','.

/Rauf Sarwar Received on Sun Sep 29 2002 - 14:30:24 CDT