Oracle: Fuzzy lookup

Question

I'm loading a table looking up an employee table. However sometimes the names from Source files and Employee table does not match correctly.

**Employee table:**
Employee Name
Paul Jaymes

**Source File**
Paul James

I want this to match. What could be the solution.

Answer 1

Use the UTL_MATCH package or the SOUNDEX function:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Employees ( Name ) AS
SELECT 'Paul Jaymes' FROM DUAL;

Query 1:

UTL_MATCH.EDIT_DISTANCE: Calculates the number of changes required to transform string-1 into string-2

SELECT *
FROM   Employees
WHERE  UTL_MATCH.EDIT_DISTANCE( Name, 'Paul James' ) < 2

Query 2:

UTL_MATCH.EDIT_DISTANCE_SIMILARITY: Calculates the number of changes required to transform string-1 into string-2, returning a value between 0 (no match) and 100 (perfect match)

SELECT *
FROM   Employees
WHERE  UTL_MATCH.EDIT_DISTANCE_SIMILARITY( Name, 'Paul James' ) > 90

Query 3:

UTL_MATCH.JARO_WINKLER: Calculates the measure of agreement between string-1 and string-2

SELECT *
FROM   Employees
WHERE  UTL_MATCH.JARO_WINKLER( Name, 'Paul James' ) > 0.9

Query 4:

UTL_MATCH.JARO_WINKLER_SIMILARITY: Calculates the measure of agreement between string-1 and string-2, returning a value between 0 (no match) and 100 (perfect match)

SELECT *
FROM   Employees
WHERE  UTL_MATCH.JARO_WINKLER_SIMILARITY( Name, 'Paul James' ) > 95

Query 5:

SOUNDEX: returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English.

SELECT *
FROM   Employees
WHERE  SOUNDEX( Name ) = SOUNDEX( 'Paul James' )

Results: All give the output:

|        NAME |
|-------------|
| Paul Jaymes |