How to split string and compare .A function equivalent to locate function in oracle.....

How to split string and compare .A function equivalent to locate function in oracle.....

First  input the string which needs to be split to the following split_str function. This function will split the given string into multiple words depending on the given delimiter.
The return will be an array  of string.

CREATE OR REPLACE function splitinput_str(v_str in varchar2, v_delimiter in varchar2) return t_split_array as
                sarray t_split_array := t_split_array();
  v_tmp varchar2(1000);
begin
  for i in 1 .. length(v_str)
  loop
    v_tmp := regexp_substr(v_str, '[^' || v_delimiter || ']+', 1, i);  
                exit when v_tmp is null;
    sarray.extend;
    sarray(i) := v_tmp;
  end loop;
 
  return sarray;
end;
/

after that we will compare two strings using the below function:-

CREATE OR REPLACE FUNCTION .LOCATE_STRING(source_string VARCHAR2,strs VARCHAR2) RETURN CHAR IS
 -- source_string varchar2(1000) := 'oracle is wonderful language';
  vfound boolean := false;
  --strs varchar2(1000) := '123 oracle bbb';
  string_array t_split_array;
begin
  string_array := splitinput_str(strs, ' ');
  for i in string_array.FIRST .. string_array.LAST loop
    if instr(source_string, string_array(i)) > 0 then
   --If source_string like '%' || string_array(i) || '%'  then
      vfound := true;
    end if;
  end loop;
 
  if vfound then
    RETURN('found');
  else
    RETURN('notfound');
  end if;
end;
/