Re: SQL*Plus..Tricky question!

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
[SQL deleted]

>Maybe somone else has a more elegant solution

I think I do:

create or replace function getname(p_fullname in varchar2) return varchar2
as

   v_index integer;
   v_type varchar2(1);
   v_result varchar2(60);

   v_index := instr(p_fullname, 'DIVISION');    v_type := 'D';
   if v_index = 0 then

     v_index := instr(p_fullname, 'OFFICE');
     v_type := 'O';

   /* Note: you only need the "if" around this next bit

      if you're not positive
      that either "DIVISION" or "OFFICE" will appear in the string */

   if v_index != 0 then
     if v_type = 'D' then
       v_result := substr(p_fullname,1,(v_index+7));
     else
       v_result := substr(p_fullname,1,(v_index+5));
     end if;

   return v_result;
end;

Note that the 7 and 5 are, respectively, one less than the lengths of the words "DIVISION" and "OFFICE."

Hope this helps.

--Matt Received on Tue Aug 22 1995 - 00:00:00 CEST