Use the UTL_MATCH package or the SOUNDEX功能:
SQL小提琴
Oracle 11g R2 架构设置:
CREATE TABLE Employees ( Name ) AS
SELECT 'Paul Jaymes' FROM DUAL;
Query 1:
UTL_MATCH.EDIT_DISTANCE
:
计算将 string-1 转换为 string-2 所需的更改数
SELECT *
FROM Employees
WHERE UTL_MATCH.EDIT_DISTANCE( Name, 'Paul James' ) < 2
Query 2:
UTL_MATCH.EDIT_DISTANCE_SIMILARITY
:
计算将 string-1 转换为 string-2 所需的更改次数,返回 0(不匹配)和 100(完全匹配)之间的值
SELECT *
FROM Employees
WHERE UTL_MATCH.EDIT_DISTANCE_SIMILARITY( Name, 'Paul James' ) > 90
Query 3:
UTL_MATCH.JARO_WINKLER
:
计算 string-1 和 string-2 之间的一致性度量
SELECT *
FROM Employees
WHERE UTL_MATCH.JARO_WINKLER( Name, 'Paul James' ) > 0.9
Query 4:
UTL_MATCH.JARO_WINKLER_SIMILARITY
:
计算 string-1 和 string-2 之间的一致性度量,返回 0(不匹配)和 100(完全匹配)之间的值
SELECT *
FROM Employees
WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY( Name, 'Paul James' ) > 95
Query 5:
SOUNDEX
:
返回包含 char 的语音表示形式的字符串。此功能可让您比较拼写不同但英语发音相似的单词。
SELECT *
FROM Employees
WHERE SOUNDEX( Name ) = SOUNDEX( 'Paul James' )
Results:
全部给出输出:
| NAME |
|-------------|
| Paul Jaymes |