Like what
Field A: ' Ab,cd,ef,gh '
Field B: ' Aa,bb,cc,dd ' does not have the same value
Field A: ' Ab,cd,ef,gh '
Field B: ' Aa,bb,cd,dd ' has the same value CD
1.CREATE OR REPLACE TYPE ty_str_split is TABLE of VARCHAR2 (4000);
2.CREATE OR REPLACE FUNCTION cux_pub_str_split (p_str in VARCHAR2, P_delimiter in VARCHAR2)
RETURN Ty_str_split pipelined
Is
J INT: = 0;
I INT: = 1;
Len INT: = 0;
Len1 INT: = 0;
STR VARCHAR2 (4000);
BEGIN
Len: = LENGTH (P_STR);
Len1: = LENGTH (P_delimiter);
While J < Len
LOOP
J: = INSTR (P_str, P_delimiter, i);
IF j = 0
Then
J: = Len;
str: = SUBSTR (P_str, i);
PIPE ROW (str);
IF I >= len
Then
EXIT;
END IF;
ELSE
str: = SUBSTR (P_str, I, j-i);
I: = j + len1;
PIPE ROW (str);
END IF;
END LOOP;
RETURN;
END Cux_pub_str_split;
3. test data can be converted into a table and then judged in the form of a table
or use Oracle to take the intersection function intersect
Select Regexp_substr (NME, ' [^,]+ ', 1, rownum) NME
From (SELECT ' Beijing, Hangzhou, Shanghai ' NME from dual)
Connect by rownum <= Length (Regexp_replace (NME, ' [^,]+ ')) +1
Intersect
Select Regexp_substr (NME, ' [^,]+ ', 1, rownum) NME
From (SELECT ' Beijing, Shanghai, Sichuan, Zhengzhou ' NME from dual)
Connect by rownum <= Length (Regexp_replace (NME, ' [^,]+ ')) +1;
Oracle two comma-separated strings how to determine if they have the same value