Oracle SQL字符串内容比对咨询：求替代UTL_MATCH的有效方法

需要比对Table_A与Table_B中String_to_compare列的字符串内容，但UTL_MATCH.JARO_WINKLER_SIMILARITY会给实际不相似的字符串（例如'this is a string'与'this is a mtrink'）打出高分，以下是几种更有效的比对方法：

1. 编辑距离（Levenshtein Distance） #

编辑距离计算将一个字符串转换为另一个所需的最少单字符操作（插入、删除、替换）次数，数值越小表示字符串越相似。Oracle无内置函数，需自定义：

CREATE OR REPLACE FUNCTION levenshtein_distance(p_str1 IN VARCHAR2, p_str2 IN VARCHAR2) RETURN NUMBER IS
    l_len1 NUMBER := LENGTH(p_str1);
    l_len2 NUMBER := LENGTH(p_str2);
    l_matrix NUMBER_TABLE;
BEGIN
    IF l_len1 = 0 THEN RETURN l_len2; END IF;
    IF l_len2 = 0 THEN RETURN l_len1; END IF;

    l_matrix := NUMBER_TABLE();
    l_matrix.EXTEND(l_len1 + 1);
    FOR i IN 0..l_len1 LOOP
        l_matrix(i) := NUMBER_TABLE();
        l_matrix(i).EXTEND(l_len2 + 1);
        l_matrix(i)(0) := i;
    END LOOP;
    FOR j IN 0..l_len2 LOOP
        l_matrix(0)(j) := j;
    END LOOP;

    FOR i IN 1..l_len1 LOOP
        FOR j IN 1..l_len2 LOOP
            l_matrix(i)(j) := LEAST(
                l_matrix(i-1)(j) + 1,
                l_matrix(i)(j-1) + 1,
                l_matrix(i-1)(j-1) + CASE WHEN SUBSTR(p_str1, i, 1) = SUBSTR(p_str2, j, 1) THEN 0 ELSE 1 END
            );
        END LOOP;
    END LOOP;

    RETURN l_matrix(l_len1)(l_len2);
END;
/

比对查询示例 #

SELECT
    a.id AS a_id,
    b.id AS b_id,
    a.String_to_compare AS a_str,
    b.String_to_compare AS b_str,
    levenshtein_distance(a.String_to_compare, b.String_to_compare) AS edit_distance
FROM Table_A a
CROSS JOIN Table_B b
ORDER BY edit_distance;

2. Jaccard相似度（基于词汇交集） #

Jaccard相似度通过计算两个字符串的词汇交集与并集的比值，衡量内容重叠度，更关注文本的核心词汇相似性：

CREATE OR REPLACE FUNCTION jaccard_similarity(p_str1 IN VARCHAR2, p_str2 IN VARCHAR2) RETURN NUMBER IS
    TYPE str_set IS TABLE OF VARCHAR2(100);
    l_set1 str_set;
    l_set2 str_set;
    l_intersect NUMBER := 0;
    l_union NUMBER := 0;
BEGIN
    -- 拆分字符串为单词（按空格分割，可根据需求调整分隔符）
    SELECT TRIM(REGEXP_SUBSTR(p_str1, '[^[:space:]]+', 1, LEVEL))
    BULK COLLECT INTO l_set1
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR(p_str1, '[^[:space:]]+', 1, LEVEL) IS NOT NULL;

    SELECT TRIM(REGEXP_SUBSTR(p_str2, '[^[:space:]]+', 1, LEVEL))
    BULK COLLECT INTO l_set2
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR(p_str2, '[^[:space:]]+', 1, LEVEL) IS NOT NULL;

    -- 计算交集数量
    FOR i IN 1..l_set1.COUNT LOOP
        IF l_set2.EXISTS(INDEX(l_set2, l_set1(i))) THEN
            l_intersect := l_intersect + 1;
        END IF;
    END LOOP;

    -- 计算并集数量
    l_union := l_set1.COUNT + l_set2.COUNT - l_intersect;

    IF l_union = 0 THEN RETURN 0; END IF;
    RETURN l_intersect / l_union;
END;
/

比对查询示例 #

SELECT
    a.id AS a_id,
    b.id AS b_id,
    a.String_to_compare AS a_str,
    b.String_to_compare AS b_str,
    ROUND(jaccard_similarity(a.String_to_compare, b.String_to_compare), 4) AS jaccard_score
FROM Table_A a
CROSS JOIN Table_B b
ORDER BY jaccard_score DESC;

3. Oracle Text 文本相似度匹配 #

Oracle Text提供专业的文本分析能力，支持同义词、词干提取、模糊匹配等，适合处理长文本的语义相似性比对：

步骤1：创建文本索引 #

CREATE INDEX idx_table_a_string ON Table_A(String_to_compare) INDEXTYPE IS CTXSYS.CONTEXT;
CREATE INDEX idx_table_b_string ON Table_B(String_to_compare) INDEXTYPE IS CTXSYS.CONTEXT;

步骤2：相似度比对查询 #

使用MATCHES操作符或CTX_SCORE.SCORE获取相似度：

SELECT
    a.id AS a_id,
    b.id AS b_id,
    a.String_to_compare AS a_str,
    b.String_to_compare AS b_str,
    CTX_SCORE.SCORE(1) AS similarity_score
FROM Table_A a, Table_B b
WHERE MATCHES(b.String_to_compare, '{' || a.String_to_compare || '}', 1) > 0
ORDER BY similarity_score DESC;

附：建表与插入数据SQL #

CREATE TABLE Table_A 
(   "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 
9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE  
NOKEEP  NOSCALE  NOT NULL ENABLE,
String_to_compare varchar2(4000)
);

CREATE TABLE Table_B 
(   "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 
9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE  
NOKEEP  NOSCALE  NOT NULL ENABLE,
String_to_compare varchar2(4000)
);

INSERT INTO Table_A (String_to_compare)
VALUES('Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed euismod diam vel 
lectus feugiat porta. Ut sit amet elit nisi. Integer at aliquam neque.');

INSERT INTO Table_A (String_to_compare)
VALUES('Phasellus lectus purus, euismod auctor tortor ut, lacinia ornare augue. Sed 
fringilla feugiat commodo. Suspendisse leo arcu, malesuada eu maximus sit amet, 
eleifend eu urna.');

INSERT INTO Table_A (String_to_compare)
VALUES('Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere 
cubilia curae');

INSERT INTO Table_B (String_to_compare)
VALUES('Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed euismod diam vel 
lectus feugiat porta. Ut sit amet elit nisi. Integer at aliquam neque.');

INSERT INTO Table_B (String_to_compare)
VALUES('Phasellus lectus purus, euismod auctor tortor ut, lacinia ornare augue. Sed 
fringilla feugiat commodo. Suspendisse leo arcu, malesuada eu maximus sit amet, 
eleifend eu urna.');

INSERT INTO Table_B (String_to_compare)
VALUES('Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere 
cubilia curae');

内容的提问来源于stack exchange，提问作者JB999