ORACLE判断两个字符串是否有相同字符

背景：

要求实现两个字符串/两列数据对比之后是否有相同的字符

实现思路：

将两个字符串进行字符拆分，成为两个列表，再进行内连接inner join；

如果count合计之后为0那么证明两个字符串之间没有字符是相同的，

如果count合计之后不为0那么证明两个字符串之间有重叠的字符；

结果：

1.普通逻辑（针对两个字符串）

例：'太原公安局分局' 和'江西省南昌市'两个字符串无相同的汉字

WITH 
  string1 AS (SELECT '太原公安局分局' AS str FROM dual),
  string2 AS (SELECT '江西省南昌市' AS str FROM dual),
  string1_characters AS (
    SELECT REGEXP_SUBSTR(str, '(.)', 1, LEVEL) AS character
    FROM string1
    CONNECT BY LEVEL <= LENGTH(str)
  ),
  string2_characters AS (
    SELECT REGEXP_SUBSTR(str, '(.)', 1, LEVEL) AS character
    FROM string2
    CONNECT BY LEVEL <= LENGTH(str)
  )
SELECT count(DISTINCT string1_characters.character)
FROM string1_characters
inner JOIN string2_characters ON string1_characters.character = string2_characters.character;

运行结果如下：

2.函数（针对表的两列数据对比）

例：对比个人的签发机关和证件地址是不是有误

CREATE OR REPLACE FUNCTION F_SPLITSTR_COM(p_string1   IN VARCHAR2, --需要处理的字符串1
                                          p_string2   IN VARCHAR2)--需要处理的字符串2
 RETURN varchar2 as
  v_return_str varchar2(2000);

BEGIN

    select count(column_values)
      into v_return_str
      from (SELECT DISTINCT a.column_values
              FROM (SELECT SUBSTR(p_string1, LEVEL, 1) AS column_values
                      FROM DUAL
                    CONNECT BY LEVEL <= LENGTH(p_string1)) a
              inner join (SELECT SUBSTR(p_string2, LEVEL, 1) AS column_values
                          FROM DUAL
                        CONNECT BY LEVEL <= LENGTH(p_string2)) b
                on a.column_values = b.column_values);
    RETURN v_return_str;

END f_splitstr_com;

案例使用及运行结果如下：

TV字段表示有几个汉字重复