Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed.news2me.com!canoe.uoregon.edu!logbridge.uoregon.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: vlaz@01p.gr (VLADMAN) Newsgroups: comp.databases.oracle.misc Subject: Re: ORACLE SUBSTR FUNCTION HELP Date: 10 Oct 2002 00:40:37 -0700 Organization: http://groups.google.com/ Lines: 34 Message-ID: References: <8f19196e.0209290611.38cc9b2@posting.google.com> NNTP-Posting-Host: 195.167.52.4 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1034235638 25284 127.0.0.1 (10 Oct 2002 07:40:38 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 10 Oct 2002 07:40:38 GMT Xref: newsfeed1.easynews.com comp.databases.oracle.misc:87633 X-Received-Date: Thu, 10 Oct 2002 00:40:33 MST (news.easynews.com) dchou1108@hotmail.com (Dave) wrote in message news:<8f19196e.0209290611.38cc9b2@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 .