Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

where contains and array

igor.burlakovAug 22 2010 — edited Aug 22 2010
I have a package that splits a string to a separate words into array:
CREATE OR REPLACE PACKAGE STRING_FNC
IS

TYPE t_array IS TABLE OF VARCHAR2(50)
   INDEX BY BINARY_INTEGER;

FUNCTION SPLIT (p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array;

END;

CREATE OR REPLACE PACKAGE BODY STRING_FNC
IS

   FUNCTION SPLIT (p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array 
   IS
   
      i       number :=0;
      pos     number :=0;
      lv_str  varchar2(50) := p_in_string;
      
   strings t_array;
   
   BEGIN
   
      -- determine first chuck of string  
      pos := instr(lv_str,p_delim,1,1);
   
      -- while there are chunks left, loop 
      WHILE ( pos != 0) LOOP
         
         -- increment counter 
         i := i + 1;
         
         -- create array element for chuck of string 
         strings(i) := substr(lv_str,1,pos);
         
         -- remove chunk from string 
         lv_str := substr(lv_str,pos+1,length(lv_str));
         
         -- determine next chunk 
         pos := instr(lv_str,p_delim,1,1);
         
         -- no last chunk, add to array 
         IF pos = 0 THEN
        
            strings(i+1) := lv_str;
         
         END IF;
      
      END LOOP;
   
      -- return array 
      RETURN strings;
      
   END SPLIT;

END;  
To test if it works I use:
DECLARE    
	str string_fnc.t_array;
BEGIN    
	str := string_fnc.split('word1 word2 word3');	

	FOR i IN 1..str.count LOOP	
		DBMS_OUTPUT.PUT_LINE(str(i));
	END LOOP;	
END;
Is there a way to use this package or split function in SELECT such as:
SELECT title
FROM t1
WHERE CONTAINS (title, HERE I PUT FUNCTION('word1 word2 word3')) > 0 ; ?

I mean, for example, If I have table with 4 rows with values word1, word2, word3 and word4 and I run this query without function, I get NO ROWS FOUND, but want to find word1, word2 and word3.
This post has been answered by Barbara Boehmer on Aug 22 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 19 2010
Added on Aug 22 2010
3 comments
2,074 views