如何在Dolt数据库中编写SQL查询移除varchar列最后一个逗号后的内容

Got it, let's resolve that duplicate unit number problem in your physical_address column. Your initial query was removing everything after the first comma, but we need to target the last comma to strip off the duplicated unit suffix.

Step 1: Test the Cleanup Logic First #

Always run a SELECT query to verify results before updating data—this avoids accidental data loss. Use this to preview cleaned addresses:

SELECT 
    physical_address AS original_address,
    CASE 
        -- Only modify addresses with commas
        WHEN CHARINDEX(',', physical_address) > 0 THEN 
            LEFT(
                physical_address,
                -- Calculate position of the last comma, truncate before it
                LEN(physical_address) - CHARINDEX(',', REVERSE(physical_address))
            )
        -- Leave comma-free addresses unchanged
        ELSE physical_address
    END AS cleaned_address
FROM sales;

Step 2: Run the Update Query #

Once you confirm the preview looks correct, execute this UPDATE to fix the actual data:

UPDATE sales
SET physical_address = CASE 
    WHEN CHARINDEX(',', physical_address) > 0 THEN 
        LEFT(
            physical_address,
            LEN(physical_address) - CHARINDEX(',', REVERSE(physical_address))
        )
    ELSE physical_address
END
-- Only update rows that need changes for better performance
WHERE CHARINDEX(',', physical_address) > 0;

How This Works #

Let’s break down the key logic:

• REVERSE(physical_address): Flips the string, so finding the first comma in the reversed text maps to the last comma in the original address.
• LEN(physical_address) - CHARINDEX(',', REVERSE(...)): Calculates the length of the address up to (but not including) the final comma, so we only keep the valid, non-duplicated portion.
• CASE statement: Ensures we don’t alter addresses that don’t have commas (like your first three example rows).
• WHERE clause: Skips rows that don’t need updates, making the query faster and avoiding unnecessary writes.

Quick Note #

Dolt is fully compatible with MySQL string functions, so all tools used here (REVERSE, CHARINDEX, LEN, LEFT, CASE) work seamlessly. Plus, Dolt’s version control lets you roll back changes easily if needed—handy for data cleanup tasks!

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