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.