Re: SQL*Plus..Tricky question!

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
something like this would work:

select substr ('HOW ARE YOU ME', 1, 
         DECODE( FLOOR( INSTR('HOW ARE YOU ME', 'YOU') /
                        INSTR('HOW ARE YOU ME', 'ME')
                      ), 
                 0, INSTR('HOW ARE YOU ME', 'YOU') + 2,
                 INSTR('HOW ARE YOU ME', 'ME') + 1
               )
              )

From the string 'HOW ARE YOU ME', it returns a sub string to the first occurance of a YOU or ME.

--
**************************************************************
*                          Saad Ahmad                        *
*                          McHugh Freeman & Associates, Inc. *
*                          Ph:  (414) 798-8606 Ext. 457      *
*                          Fax: (414) 798-8619               *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************