Oracle-L: pl/sql question and owa

From: Guang Mei <gmei_at_incyte.com>
Date: Thu, 20 Nov 2003 19:39:55 -0800
Message-ID: <F001.005D74EB.20031120193955@fatcity.com>

Hi:

In my pl/sql program, I want to process each "word" in a string. The string is selected from a varchar2(300) column. The delimit that separates the words is not necessary space character. The definition of the delimit in this program is set as

Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) and
the character is not one of these: '-.,/<*>_'

Now my program is basically checking each character, find the delimit, and rebuild each word. After that I process each "word". The code looks like this:

str := "This will be a long string with length upto 300 characters, it may contain some invisible characters';
len := length(str)+1;
  for i in 1..len loop
    ch := substr(str,i,1);
    if (not strings.isAlnum(ch) and instr('-.,/<*>_', ch)<1) then

      if word is not null then
        -- do some processing to variable word !
        word := null;    -- reset it
      end if;
    else
      word := word || ch;   -- concat ch to word

end if;
end loop;

I think It's taking too long because it loops through each characters. I hope I could find a way to speed it up. I don't have experiience in owa_pattern, but I thought there might be a way to do it here:

str := "This will be a long string with length upto 300 characters, it may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop

pos := owa_pattern.amatch(newstr, 1, '\W'); -- how can I mask out these '-.,/<*>_' ???

word := substr(newstr, 1, pos-1);

do some processing to variable word ! if pos != 0 then newstr := substr(newstr, pos+1); end if; end loop;

My simple tests showed that owa_pattern call is much slower than direct string manupilation. But I would like to try it in this case if I could easily get the "wrods" from the string. Any suggestions?

TIA. Guang

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Guang Mei
INET: gmei_at_incyte.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Nov 20 2003 - 21:39:55 CST

pl/sql question and owa_pattern question