oracle 关于判断两个字符串是否存在交集的方法

**

oracle 关于判断两个字符串是否存在交集的方法

**
实例是比如针对 字符串： “GEN/122/234” 和 “123/GEN/456”判断是否存在交集
**

CREATE OR REPLACE FUNCTION cux_pub_str_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
  return varchar2 is
  Result varchar2(2000);
begin
    for a in (select column_value from table(FN_split_str(p_str, '/'))) loop
    for b in (select column_value from table(FN_split_str(p_delimiter, '/'))) loop
       if a.column_value = b.column_value then 
				    Result := '1';
            EXIT;
						end if;
    end loop; 
		end loop; 
		if Result is null then
            Result := '0';
          end if;
  return(Result);
END cux_pub_str_split;

**
其中的FN_split_str 是自写的根据 相应符号拆分 上面的代码是根据’/’ 进行拆分 p_str，代码如下：

CREATE OR REPLACE FUNCTION FN_SPLIT_STR(p_string IN CLOB, p_delimiter IN VARCHAR2)
    RETURN TYPE_STR_LIST
    PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
    v_line     CLOB;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN
            v_line := TRIM(SUBSTR(p_string, v_start));
            IF v_line IS NOT NULL THEN
                PIPE ROW(UPPER(v_line));
            END IF;
            v_start := v_length + 1;
        ELSE
            v_line := TRIM(SUBSTR(p_string, v_start, v_index - v_start));
            IF v_line IS NOT NULL THEN
                PIPE ROW(UPPER(v_line));
            END IF;
            v_start := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END FN_SPLIT_STR;