Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: ORACLE SUBSTR FUNCTION HELP
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