Re: SQL*Plus..Tricky question!

In <41betl$oqh_at_odin.community.net> ericl_at_community.net writes:
>
>
>In article <40qdci$ln5_at_cville-srv.wam.umd.edu>,
>Wascley Wabbit <beefstew_at_exp2.wam.umd.edu> wrote:
>>okay, here's a tricky question for all you Sql*Plus people:
>>
>>I have a 60 character field (char) that will look like this:
>> CONTRACTING DIVISION OFFICE OF THE CHEIEF
>> FA DIVISION OFFICE OF THE CHIEF
>> RESOURCE MANAGEMENET DIVISION OFFICE OF THE CHIEF
>> INFROMATION MANAGEMENT OFFICE
>> PUBLIC AFFAIRS OFFICE OF THE CHIEF
>>
>>What I'm trying to do is to only take the charcters up to divison or
 up
>>to office whatever comes first, to get the folllowing output:
>> CONTRACTING DIVISION
>> FA DIVISION
>> RESOURCE MANAGEMENT DIVISION
>> INFROMATION MANAGEMENT OFFICE
>> PUBLIC AFFAIRS OFFICE
>>
>>Can this be done with substr or what?
>>
>
>This is really ugly but it works
>
>select substr(column,1,instr(column,' OFFICE OF THE CHIEF'))
>from table
>where (column like '%DIVISION%'
>or column like '%OFFICE OFFICE%')
>and column like '%OFFICE OF THE CHIEF%'
>
>union
>
>select substr(column,1,instr(dept_name,' OF THE CHIEF'))
>from table
>where column like '% OFFICE%'
>and (column not like '%OFFICE OFFICE%'
>and column not like '%DIVISION%' )
>and column like '%OF THE CHIEF%'
>
>union
>
>select column
>from table
>where column not like '%OF THE CHIEF%'
>
>Maybe somone else has a more elegant solution
>
>
>Regards
>
>
>Eric
Or you can try this :

Select substr(c1, 1, least(instr(c1,'DIVISION'), instr(c1,'OFFICE')) from ....

Sai Lim
Ann Arbor MI
313 761 4700 ext 3400
or slim_at_umi.com Received on Wed Aug 23 1995 - 00:00:00 CEST