Re: SQL*Plus..Tricky question!

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 Received on Tue Aug 22 1995 - 00:00:00 CEST