Re: ORACLE SUBSTR FUNCTION HELP

From: Glen A Stromquist <glen_stromquist_at_nospam.yahoo.com>
Date: Sun, 29 Sep 2002 17:41:54 GMT
Message-ID: <CXGl9.14106$OO4.665821@news1.telusplanet.net>

Sybrand Bakker wrote:

> 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

It depends on what you are trying to do, but if you are querying the data and want to sort by city, the only way I can see this happening is to export the columns you want by spooling the query to a file, then using excel's (or something equivilant) text to columns feature, which will usually do a good job of separating a flat file in to columns. Then once you have it parsed the way you want, query away.

Other than that, a redesign of the table(s) in question is probably in order.

HTH Received on Sun Sep 29 2002 - 12:41:54 CDT