Oracle Find_in_set functions

Create or replace FUNCTION find_in_set (piv_str1 varchar2, piv_str2 varchar2, p_sep varchar2: = '; ')

RETURN number is

L_idx number:=0; --used to calculate the position of separators in PIV_STR2

STR VARCHAR2 (500); --sub-string truncated by delimiter

Piv_str varchar2 (+): = PIV_STR2; --Assign PIV_STR2 to Piv_str

Res number:=0; --Return results

Loopindex number:=0;

BEGIN

--if there are no delimiters in the PIV_STR, directly determine whether PIV_STR1 and piv_str are equal, equal Res=1

IF InStr (Piv_str, P_SEP, 1) = 0 Then

IF piv_str = Piv_str1 Then

Res:= 1;

END IF;

ELSE

--cycle by delimiter intercept piv_str

LOOP

L_idx: = InStr (PIV_STR,P_SEP);

loopindex:=loopindex+1;

--when there are separators in the PIV_STR

IF l_idx > 0 Then

--Truncate the field before the first delimiter str

str:= substr (piv_str,1,l_idx-1);

--Determine if STR and piv_str1 are equal, equal res=1 and end the cyclic judgment

IF str = PIV_STR1 Then

Res:= Loopindex;

EXIT;

END IF;

PIV_STR: = substr (Piv_str,l_idx+length (P_SEP));

ELSE

--when there are no delimiters in the truncated piv_str, determine if piv_str and piv_str1 are equal, equal Res=1

IF piv_str = Piv_str1 Then

Res:= Loopindex;

END IF;

-regardless of whether the final is equal, jump out of the loop

EXIT;

END IF;

END LOOP;

--End Loop

END IF;

--Return Res

RETURN Res;

END Find_in_set;

How to use: Select Find_in_set (' 4 ', ' 1;2;3;4 ') from dual;

Select Eng_name,industry_sector from Hky_eng he WHERE find_in_set (' Chemical and petrochemical medicine ', he. Industry_sector) >0;

Oracle Find_in_set functions