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;
/
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;
/
