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
FYI:
SELECT ADDRESS AS THE_STRING,
INSTR(ADDRESS,'.')+1 AS DOT_NEXT_POS,
INSTR(ADDRESS,',') AS COMMA_POS,
INSTR(ADDRESS,',') -
( INSTR(ADDRESS,'.')+1 ) AS NUM_OF_CHARS,
LTRIM(RTRIM(SUBSTR(ADDRESS, -- THE_STRING
INSTR(ADDRESS,'.')+1, -- DOT_NEXT_POS
INSTR(ADDRESS,',') - -- NUM_OF_
(INSTR(ADDRESS,'.')+1) -- CHARS
)
) ) AS THE_CITY
FROM MY_TABLE
HOPE I HAVEN'T READ YOUR MESSAGE , TOO LATE .
Received on Thu Oct 10 2002 - 02:40:37 CDT
