DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: need a piece of pl/sql to do this.....

  1. #1
    Join Date
    Oct 2000
    Posts
    103

    need a piece of pl/sql to do this.....

    a varchar2(200) field gets passed in with a bunch of text in it. I need to scan the text

    for instances
    of a "special char" if one or more is found it needs to replace the following text up

    until the next blank space with ++++ as an example. my rough flow of it is below. does

    any one know of anything existing like this?


    grab text field
    look for(1st, 2nd, 3rd, ...) speacial char

    if
    find 1st special char(example: @ )
    and NOT eof (end of varchar2 200)
    replace string following it until next blank space(starting at the char
    after special char and go to first space and replace with newval)
    special char++

    else exit
    return changed varchar2(200)



    thanks in advance
    SM

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Well, all you need are the following functions.

    InStr() to find the first occurance of the special character, and the first occurance of space following the first special character

    SubStr() to snip out sections of the string

    RPad() to pad out a string to a given length using a particular character
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    The following example replaces abbreviations with un-abbreviated words:
    Code:
    drop table full_job_ttl
    /
    create table full_job_ttl
    as
    select * from job_ttl
    /
    alter table full_job_ttl
      add (full_title varchar2(200))
    /
    set serverout on size 1000000;
    declare
    type txt_typ is table of varchar2(200)
         index by binary_integer;
    type rowid_typ is table of rowid
         index by binary_integer;
    v_abb txt_typ;
    v_una txt_typ;
    v_ttl txt_typ;
    v_full_ttl txt_typ;
    v_row rowid_typ;
    i pls_integer;
    j pls_integer;
    k pls_integer;
    l pls_integer;
    begin
      select abbrev, un_abbrev  bulk collect
        into v_abb, v_una
        from job_ttl_abbrev
      ;
      select rowid, title  bulk collect
        into v_row, v_ttl
        from full_job_ttl
      ;
      for j in 1..v_abb.count
      loop
        if NVL(v_una(j),'?') = '?' then
          v_una(j):='?'||v_abb(j);
        end if;
      end loop;
      for i in 1..v_row.count
      loop
        v_full_ttl(i):=RTRIM(UPPER(v_ttl(i)));
        for j in 1..v_abb.count
        loop
          if length(v_full_ttl(i)) > length(v_abb(j)) and
             substr(v_full_ttl(i),1,length(v_abb(j))+1) = v_abb(j)||' ' then
            v_full_ttl(i):=v_una(j)||substr(v_full_ttl(i),length(v_abb(j))+1);
          end if;
          k:=instr(upper(v_full_ttl(i)),' '||v_abb(j)||' ');
          if k>0 then
            v_full_ttl(i):=substr(v_full_ttl(i),1,k)||v_una(j)
                         ||substr(v_full_ttl(i),k+length(v_abb(j))+1);
          end if;
          k:= length(v_full_ttl(i)) - length(v_abb(j));
          if length(v_full_ttl(i)) > length(v_abb(j)) and
             substr(v_full_ttl(i),k) = ' '||v_abb(j) then
            v_full_ttl(i):=substr(v_full_ttl(i),1,k)||v_una(j);
          end if;
        end loop;
        v_full_ttl(i):=RTRIM(INITCAP(v_full_ttl(i)));
        dbms_output.put_line(v_ttl(i)||' -> '||v_full_ttl(i));
      end loop;
      forall i in  1..v_row.count
        update full_job_ttl
           set full_title = v_full_ttl(i)
         where rowid = v_row(i)
      ;
      commit;
    end;
    /
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Oct 2000
    Posts
    103
    thanks guys

    wow, its code like that that makes me happy im not a programer.....but unhappy I have to figure it out for this piece :(

    this is going to be a challenge...

    --SM

  5. #5
    Join Date
    Oct 2000
    Posts
    103
    *Deleted*
    sorry, double post
    --SM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width