如何使用Oracle PL/SQL去除地址中的重复单词？

Let's break down how to handle the two duplicate address scenarios you described, and build a reusable PL/SQL function to clean them up.

First, Let's Clarify the Scenarios #

1. Even-length full duplication: e.g., '3 Mayers Court 3 Mayers Court' — total word count is even, and the first half exactly matches the second half.
2. Odd-length partial duplication: e.g., 'Manor House Manor' or '1 Briar Cottages 1 Briar' — total word count is odd, with a single middle word, and the words before it exactly match the words after it.

The Approach #

The core idea is to:

• Split the address into individual words (handling extra spaces first)
• Check if the duplicate pattern exists based on word count parity
• Reconstruct the address with the duplicate segment removed if the pattern matches

PL/SQL Function Implementation #

CREATE OR REPLACE FUNCTION clean_duplicate_address(p_address IN VARCHAR2) 
RETURN VARCHAR2 
IS
    TYPE t_word_list IS TABLE OF VARCHAR2(100);
    l_words t_word_list;
    l_clean_address VARCHAR2(1000);
    l_word_count NUMBER;
    l_mid_pos NUMBER;
    l_match BOOLEAN := TRUE;
BEGIN
    -- Handle null or empty input
    IF p_address IS NULL OR TRIM(p_address) = '' THEN
        RETURN p_address;
    END IF;

    -- Normalize spaces (replace multiple spaces with single space)
    l_clean_address := REGEXP_REPLACE(p_address, '\s+', ' ');
    
    -- Split address into individual words
    SELECT REGEXP_SUBSTR(l_clean_address, '[^ ]+', 1, LEVEL)
    BULK COLLECT INTO l_words
    FROM DUAL
    CONNECT BY LEVEL <= REGEXP_COUNT(l_clean_address, '[^ ]+');
    
    l_word_count := l_words.COUNT;
    
    -- Case 1: Even word count (full first-half duplication)
    IF MOD(l_word_count, 2) = 0 THEN
        -- Check if first half matches second half
        FOR i IN 1..l_word_count/2 LOOP
            IF l_words(i) != l_words(i + l_word_count/2) THEN
                l_match := FALSE;
                EXIT;
            END IF;
        END LOOP;
        
        -- If match, return first half; else return original normalized address
        IF l_match THEN
            l_clean_address := '';
            FOR i IN 1..l_word_count/2 LOOP
                l_clean_address := l_clean_address || ' ' || l_words(i);
            END LOOP;
            l_clean_address := TRIM(l_clean_address);
        END IF;
    ELSE
        -- Case 2: Odd word count (duplication around middle word)
        l_mid_pos := (l_word_count + 1)/2;
        
        -- Check if words before middle match words after middle
        FOR i IN 1..l_mid_pos - 1 LOOP
            IF l_words(i) != l_words(l_mid_pos + i) THEN
                l_match := FALSE;
                EXIT;
            END IF;
        END LOOP;
        
        -- If match, return first half + middle word; else return original normalized address
        IF l_match THEN
            l_clean_address := '';
            FOR i IN 1..l_mid_pos LOOP
                l_clean_address := l_clean_address || ' ' || l_words(i);
            END LOOP;
            l_clean_address := TRIM(l_clean_address);
        END IF;
    END IF;
    
    RETURN l_clean_address;
EXCEPTION
    WHEN OTHERS THEN
        -- Return original address if any error occurs
        RETURN p_address;
END;
/

Testing the Function #

You can test it with your sample addresses like this:

-- Test even-length case
SELECT clean_duplicate_address('3 Mayers Court 3 Mayers Court') AS cleaned_address FROM DUAL;
-- Output: '3 Mayers Court'

-- Test odd-length case 1
SELECT clean_duplicate_address('Manor House Manor') AS cleaned_address FROM DUAL;
-- Output: 'Manor House'

-- Test odd-length case 2
SELECT clean_duplicate_address('1 Briar Cottages 1 Briar') AS cleaned_address FROM DUAL;
-- Output: '1 Briar Cottages'

-- Test non-duplicate address (returns original)
SELECT clean_duplicate_address('123 Main St') AS cleaned_address FROM DUAL;
-- Output: '123 Main St'

Notes #

• The function handles extra spaces in the input by normalizing them to single spaces first.
• If the address doesn't match either duplicate pattern, it returns the original normalized address.
• Error handling ensures that any unexpected issues (like very long words, invalid characters) won't break the function — it just returns the original address.

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