Oracle正则表达式：逐次截断columnB末尾字符匹配columnA

Got it, let's work through this problem step by step. You need to match columnA from one table with columnB from another—if they don't match right away, you keep chopping off the last character of columnB and try again until either a match sticks or columnB is empty. Here's how to pull this off with SQL, since this is a common database matching scenario.

Core Logic Recap #

• For every value in columnB, generate all possible truncated versions (starting from the full string down to an empty string)
• Find the longest possible truncated columnB that matches any columnA (since we want the closest match first)
• Handle cases where no match exists at all

Example Implementation (Using Recursive CTE) #

Recursive Common Table Expressions (CTEs) are perfect here because they let us iterate through each truncation step. This works in PostgreSQL, MySQL 8.0+, SQL Server, and other modern databases.

WITH RECURSIVE truncated_b AS (
    -- Start with the full columnB values
    SELECT 
        b.id,
        b.columnB AS current_b,
        b.columnB AS original_b,
        CHAR_LENGTH(b.columnB) AS remaining_length
    FROM table_b b
    
    UNION ALL
    
    -- Recursively truncate the last character each time
    SELECT 
        tb.id,
        LEFT(tb.current_b, tb.remaining_length - 1),
        tb.original_b,
        tb.remaining_length - 1
    FROM truncated_b tb
    WHERE tb.remaining_length > 0 -- Stop when we hit empty string
)
-- Now match truncated versions to columnA and pick the best match
SELECT 
    a.columnA,
    tb.original_b,
    tb.current_b AS matched_truncated_b,
    -- Mark if no match was found
    CASE WHEN tb.current_b IS NULL THEN 'No match available' ELSE 'Match found' END AS match_status
FROM table_a a
LEFT JOIN (
    -- For each original columnB, keep only the longest matching truncation
    SELECT 
        original_b,
        current_b,
        ROW_NUMBER() OVER (PARTITION BY original_b ORDER BY remaining_length DESC) AS rn
    FROM truncated_b tb
    JOIN table_a a ON tb.current_b = a.columnA
) tb ON a.columnA = tb.current_b AND tb.rn = 1;

How This Works #

1. Recursive CTE (truncated_b): This generates every possible truncated version of each columnB value. For example, if columnB is "ABCDEF", it creates "ABCDEF", "ABCDE", "ABCD", ..., "", along with tracking how many characters are left.
2. Matching & Filtering: The subquery uses ROW_NUMBER() to ensure we only keep the longest matching truncation for each original columnB (since we order by remaining_length DESC, the first row is the longest valid match).
3. Left Join: This lets us include all columnA values, even if no matching columnB (truncated or not) exists.

Key Notes #

• Performance: If your tables are large, make sure columnA and columnB have indexes—this will speed up the join between the truncated values and table_a.
• Empty Strings: The CTE stops when remaining_length hits 0, so empty strings won't be checked unless you adjust the WHERE clause to include remaining_length >= 0 (but usually empty matches aren't useful).
• Case Sensitivity: Depending on your database settings, matches might be case-sensitive. If you need case-insensitive matching, wrap both columns in a case-conversion function like LOWER() or UPPER() in the join condition.

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