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?
>

Use the following or a variant thereof

select

	rtrim(
		decode(instr(column_name,'DIVISION')
			,
			0,replace(column_name,'OFFICE OF THE CHIEF','') 
			,
			replace(column_name,'OF THE CHIEF','') 
		)
	) as desired_value
from
	table_name

Regards,

Gary

-- 

 ------------------------------------------------------------
| Gary Gapinski              | email: gapinski_at_lerc.nasa.gov |
| NASA Lewis Research Center | voice: +1 216 433 5251        |
 ------------------------------------------------------------