Oracle字符串转换：字母转数值分数及姓名数据模糊匹配技术问询

Great question! When dealing with messy name datasets, combining numeric scoring for anagram-like matches and fuzzy matching for duplicate-character variations is a solid approach. Let’s tackle each scenario with practical Oracle-specific solutions:

1. Grouping Anagram Names (e.g., "Sung" vs "Snug") via Numeric Scoring #

Your initial idea of converting characters to numeric values and matching sums is perfect for identifying anagram names. Here’s how to implement this in Oracle:

Step 1: Create a Function to Calculate Character Numeric Sum #

This function assigns each letter a value (A=1, B=2, ..., Z=26) and sums them for any input string. We normalize the input to uppercase to avoid case sensitivity.

CREATE OR REPLACE FUNCTION GET_CHAR_SCORE(p_name IN VARCHAR2) RETURN NUMBER IS
    v_score NUMBER := 0;
    v_char CHAR(1);
BEGIN
    FOR i IN 1..LENGTH(UPPER(p_name)) LOOP
        v_char := SUBSTR(UPPER(p_name), i, 1);
        -- Only process alphabetic characters; ignore spaces/symbols if needed
        IF v_char BETWEEN 'A' AND 'Z' THEN
            v_score := v_score + (ASCII(v_char) - ASCII('A') + 1);
        END IF;
    END LOOP;
    RETURN v_score;
END GET_CHAR_SCORE;
/

Step 2: Group Names Using the Score #

Use this function in a GROUP BY clause to cluster anagram names together:

SELECT 
    GET_CHAR_SCORE(name) AS name_score,
    LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS matching_names
FROM your_name_table
GROUP BY GET_CHAR_SCORE(name)
HAVING COUNT(*) > 1; -- Show only groups with potential matches

Note: This works well for exact anagrams, but be aware that different names could coincidentally have the same sum (a rare edge case). Pair this with fuzzy matching for extra precision if needed.

2. Fuzzy Matching for Names with Duplicate Characters (e.g., "Lillly" vs "Lilly") #

For names with extra repeated characters, Oracle’s UTL_MATCH package provides powerful fuzzy matching utilities. We can combine this with regex preprocessing to clean up duplicate characters first.

Option 1: Use Jaro-Winkler Similarity Directly #

The Jaro-Winkler algorithm is ideal for name matching because it prioritizes prefix matches. Set a similarity threshold (e.g., 90%) to flag close matches:

SELECT 
    name1,
    name2,
    UTL_MATCH.JARO_WINKLER_SIMILARITY(name1, name2) AS similarity_score
FROM (
    SELECT t1.name AS name1, t2.name AS name2
    FROM your_name_table t1
    JOIN your_name_table t2 ON t1.name < t2.name -- Avoid duplicate pairs
)
WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(name1, name2) >= 90;

Option 2: Preprocess Names to Remove Repeated Characters #

First, clean up consecutive duplicate characters with a regex function, then run the fuzzy match:

CREATE OR REPLACE FUNCTION REMOVE_DUPLICATE_CHARS(p_name IN VARCHAR2) RETURN VARCHAR2 IS
    v_cleaned_name VARCHAR2(100);
BEGIN
    -- Replace consecutive duplicate characters with a single instance
    SELECT REGEXP_REPLACE(UPPER(p_name), '(.)\1+', '\1') INTO v_cleaned_name FROM DUAL;
    RETURN v_cleaned_name;
END REMOVE_DUPLICATE_CHARS;
/

-- Match using cleaned names
SELECT 
    t1.name AS original_name1,
    t2.name AS original_name2,
    REMOVE_DUPLICATE_CHARS(t1.name) AS cleaned_name1,
    REMOVE_DUPLICATE_CHARS(t2.name) AS cleaned_name2,
    UTL_MATCH.JARO_WINKLER_SIMILARITY(REMOVE_DUPLICATE_CHARS(t1.name), REMOVE_DUPLICATE_CHARS(t2.name)) AS similarity_score
FROM your_name_table t1
JOIN your_name_table t2 ON t1.name < t2.name
WHERE REMOVE_DUPLICATE_CHARS(t1.name) = REMOVE_DUPLICATE_CHARS(t2.name)
   OR UTL_MATCH.JARO_WINKLER_SIMILARITY(t1.name, t2.name) >= 85;

Combine Both Techniques for Robust Matching #

For maximum accuracy, merge the numeric scoring and fuzzy matching to filter anagrams first, then refine with fuzzy checks to eliminate false positives:

SELECT 
    t1.name,
    t2.name,
    GET_CHAR_SCORE(t1.name) AS score1,
    GET_CHAR_SCORE(t2.name) AS score2,
    UTL_MATCH.JARO_WINKLER_SIMILARITY(t1.name, t2.name) AS similarity
FROM your_name_table t1
JOIN your_name_table t2 ON t1.name != t2.name
WHERE GET_CHAR_SCORE(t1.name) = GET_CHAR_SCORE(t2.name) -- Anagram check
  AND UTL_MATCH.JARO_WINKLER_SIMILARITY(t1.name, t2.name) >= 80; -- Fuzzy threshold

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