在处理外部输入、数据集成或数据清洗等场景中，经常会遇到因拼写错误、缩写或格式不一致导致的字符串无法精确匹配的问题。例如，John Smith与Jon Smyth虽然指代同一个人，但简单的等值比较无法识别其关联性。PolarDB PostgreSQL版（兼容Oracle）提供的UTL_MATCH内置包解决了这一问题。它通过Jaro-Winkler算法计算两个字符串的相似度，使您能够直接在数据库中实现模糊匹配、数据去重和数据清洗等功能。
功能简介
UTL_MATCH是一个高度兼容Oracle的内置工具包，用于计算两个字符串之间的相似度。它基于Jaro-Winkler算法，该算法尤其适用于比较姓名、地址等短字符串，因为它会对具有相同前缀的字符串给予更高的相似度分数。
该包主要包含以下两个核心函数：
函数 | 返回类型 | 描述 |
|
| 计算两个字符串的Jaro-Winkler相似度分数。返回值在0.0到1.0之间，1.0表示完全相同。 |
|
| 计算两个字符串的Jaro-Winkler相似度百分比。返回值范围在0到100之间，100表示完全相同。该值是 |
算法特点
对字符串开头的匹配字符给予更高权重。
对字符顺序敏感，但允许一定程度的字符换位。
大小写不敏感。例如，
'Hello'和'hello'的相似度为100%。
适用范围
您的PolarDB PostgreSQL版（兼容Oracle）集群的修订版本需为2.0.14.17.36.0及以上
优势
数据库原生处理：直接在数据库内部进行字符串模糊匹配，无需将数据传输到应用层处理，简化了系统架构并提升了性能。
高效的数据清洗：能够快速识别因拼写错误、缩写或格式变体导致的潜在重复数据，是数据质量管理和数据去重工作的有力工具。
注意事项
空值（NULL）和空字符串处理：当JARO_WINKLER或JARO_WINKLER_SIMILARITY函数的任意一个输入参数为NULL或空字符串时，函数的返回值将是NULL，而不是0。在编写逻辑时请注意处理这种情况，避免因非预期的NULL值导致逻辑错误。
使用示例
以下示例展示了UTL_MATCH在不同业务场景中的应用。
示例1：计算基础字符串相似度
本示例演示如何使用UTL_MATCH的两个核心函数来比较不同字符串的相似度。
DECLARE
v_score BINARY_DOUBLE;
v_percent PLS_INTEGER;
BEGIN
-- 1. 比较完全相同的字符串
v_score := UTL_MATCH.JARO_WINKLER('hello', 'hello');
v_percent := UTL_MATCH.JARO_WINKLER_SIMILARITY('hello', 'hello');
DBMS_OUTPUT.PUT_LINE('完全相同 (hello vs hello) - 分数: ' || v_score || ', 百分比: ' || v_percent);
-- 2. 比较相似但有拼写错误的字符串
v_score := UTL_MATCH.JARO_WINKLER('hello', 'hallo');
v_percent := UTL_MATCH.JARO_WINKLER_SIMILARITY('hello', 'hallo');
DBMS_OUTPUT.PUT_LINE('拼写错误 (hello vs hallo) - 分数: ' || v_score || ', 百分比: ' || v_percent);
-- 3. 比较完全不同的字符串
v_score := UTL_MATCH.JARO_WINKLER('hello', 'world');
v_percent := UTL_MATCH.JARO_WINKLER_SIMILARITY('hello', 'world');
DBMS_OUTPUT.PUT_LINE('完全不同 (hello vs world) - 分数: ' || v_score || ', 百分比: ' || v_percent);
-- 4. 比较大小写不同的字符串
v_score := UTL_MATCH.JARO_WINKLER('Hello', 'hello');
v_percent := UTL_MATCH.JARO_WINKLER_SIMILARITY('Hello', 'hello');
DBMS_OUTPUT.PUT_LINE('大小写不同 (Hello vs hello) - 分数: ' || v_score || ', 百分比: ' || v_percent);
END;
/预期输出：
完全相同 (hello vs hello) - 分数: 1, 百分比: 100
拼写错误 (hello vs hallo) - 分数: 0.88, 百分比: 88
完全不同 (hello vs world) - 分数: 0.466666666666667, 百分比: 47
大小写不同 (Hello vs hello) - 分数: 1, 百分比: 100示例2：在数据清洗中进行姓名模糊匹配
本示例演示如何使用UTL_MATCH对数据集中的姓名进行模糊匹配和筛选。通过设置一个相似度阈值，可以识别出可能是同一个人的不同拼写记录。
DECLARE
TYPE name_array IS VARRAY(10) OF VARCHAR2(100);
v_standard_name VARCHAR2(100) := 'John Smith';
v_test_names name_array := name_array(
'John Smith', -- 完全匹配
'Jon Smith', -- 名字拼写错误
'John Smyth', -- 姓氏拼写变体
'J Smith', -- 名字缩写
'Smith John', -- 顺序颠倒
'John Johnson' -- 姓氏完全不同
);
v_similarity BINARY_DOUBLE;
v_threshold BINARY_DOUBLE := 0.8; -- 定义相似度阈值为80%
BEGIN
DBMS_OUTPUT.PUT_LINE('标准姓名: ' || v_standard_name || ', 匹配阈值: ' || v_threshold);
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
FOR i IN 1..v_test_names.COUNT LOOP
v_similarity := UTL_MATCH.JARO_WINKLER(v_standard_name, v_test_names(i));
DBMS_OUTPUT.PUT_LINE(
'输入: ' || RPAD(v_test_names(i), 15) ||
' | 相似度: ' || ROUND(v_similarity, 3) ||
' | 是否匹配: ' || CASE WHEN v_similarity >= v_threshold THEN '是' ELSE '否' END
);
END LOOP;
END;
/预期输出：
标准姓名: John Smith, 匹配阈值: 0.8
----------------------------------------
输入: John Smith | 相似度: 1 | 是否匹配: 是
输入: Jon Smith | 相似度: 0.973 | 是否匹配: 是
输入: John Smyth | 相似度: 0.96 | 是否匹配: 是
输入: J Smith | 相似度: 0.781 | 是否匹配: 否
输入: Smith John | 相似度: 0.533 | 是否匹配: 否
输入: John Johnson | 相似度: 0.828 | 是否匹配: 是结果分析：Jaro-Winkler算法对前缀匹配和少量字符变化容忍度高，因此Jon Smith和John Smyth被识别为高度相似。而词序颠倒的Smith John相似度则显著降低。
示例3：检测潜在的重复客户记录
在客户关系管理（CRM）系统中，可能存在因不同渠道录入而产生的重复客户记录。本示例演示如何结合姓名和电子邮件两个字段的相似度，来识别潜在的重复记录。
DECLARE
TYPE customer IS RECORD (id INTEGER, name VARCHAR2(100), email VARCHAR2(100));
TYPE customer_array IS TABLE OF customer;
v_customers customer_array := customer_array(
customer(1, 'John Smith', 'john.smith@email.com'),
customer(2, 'Jon Smith', 'j.smith@email.com'), -- 姓名相似，邮箱相似
customer(3, 'John Smyth', 'john.smyth@gmail.com'), -- 姓名相似，邮箱不同
customer(4, 'Jane Doe', 'jane.doe@email.com'), -- 完全不同
customer(5, 'John Smith', 'johnsmith@email.com') -- 姓名相同，邮箱相似
);
v_name_sim BINARY_DOUBLE;
v_email_sim BINARY_DOUBLE;
v_overall_sim BINARY_DOUBLE;
v_threshold BINARY_DOUBLE := 0.85; -- 定义综合相似度阈值
BEGIN
DBMS_OUTPUT.PUT_LINE('重复数据检测分析 (阈值: ' || v_threshold || ')');
DBMS_OUTPUT.PUT_LINE('==============================================');
FOR i IN 1..v_customers.COUNT LOOP
FOR j IN i + 1..v_customers.COUNT LOOP
v_name_sim := UTL_MATCH.JARO_WINKLER(v_customers(i).name, v_customers(j).name);
v_email_sim := UTL_MATCH.JARO_WINKLER(v_customers(i).email, v_customers(j).email);
-- 使用加权平均计算综合相似度（姓名权重70%，邮箱权重30%）
v_overall_sim := (v_name_sim * 0.7) + (v_email_sim * 0.3);
IF v_overall_sim >= v_threshold THEN
DBMS_OUTPUT.PUT_LINE('发现潜在重复:');
DBMS_OUTPUT.PUT_LINE(' 记录 ' || v_customers(i).id || ': ' || v_customers(i).name || ' | ' || v_customers(i).email);
DBMS_OUTPUT.PUT_LINE(' 记录 ' || v_customers(j).id || ': ' || v_customers(j).name || ' | ' || v_customers(j).email);
DBMS_OUTPUT.PUT_LINE(' -> 综合相似度: ' || ROUND(v_overall_sim * 100) || '%');
DBMS_OUTPUT.PUT_LINE('');
END IF;
END LOOP;
END LOOP;
END;
/预期输出：
重复数据检测分析 (阈值: 0.85)
==============================================
发现潜在重复:
记录 1: John Smith | john.smith@email.com
记录 2: Jon Smith | j.smith@email.com
-> 综合相似度: 95%
发现潜在重复:
记录 1: John Smith | john.smith@email.com
记录 3: John Smyth | john.smyth@gmail.com
-> 综合相似度: 95%
发现潜在重复:
记录 1: John Smith | john.smith@email.com
记录 5: John Smith | johnsmith@email.com
-> 综合相似度: 100%
发现潜在重复:
记录 2: Jon Smith | j.smith@email.com
记录 3: John Smyth | john.smyth@gmail.com
-> 综合相似度: 90%
发现潜在重复:
记录 2: Jon Smith | j.smith@email.com
记录 5: John Smith | johnsmith@email.com
-> 综合相似度: 95%
发现潜在重复:
记录 3: John Smyth | john.smyth@gmail.com
记录 5: John Smith | johnsmith@email.com
-> 综合相似度: 95%