求助：使用正则表达式批量编辑SQL列内容（DB Browser for SQL）

Hey there! Since you're new to SQL and stuck with DB Browser for SQL, let's walk through exactly how to batch update that column to clone string2 and insert it as a new entry right after. Here's a step-by-step solution tailored for SQLite (which DB Browser for SQL uses by default):

第一步：先备份你的数据库！ #

Before doing anything else, make a copy of your database file. If something goes wrong, you'll be glad you have a fallback. This is non-negotiable when modifying large datasets!

第二步：测试处理逻辑（先查询，不修改） #

First, let's run a read-only query to verify that the output matches what you want. Replace your_table with your actual table name, and target_column with the name of the column holding those slash-separated strings:

SELECT 
    target_column,
    -- Logic to clone string2 and insert it after itself
    CASE 
        -- Scenario 1: The string has 3+ slashes (so there's a string3 and beyond)
        WHEN (LENGTH(target_column) - LENGTH(REPLACE(target_column, '/', ''))) >= 3 THEN
            -- Grab everything up to the end of string2
            SUBSTR(target_column, 1, INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2) + INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2)) 
            -- Add the cloned string2
            || '/' || SUBSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2) + 1)
            -- Add everything after string2
            || SUBSTR(target_column, INSTR(SUBSTR(target_column, 1, INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2) + 1), '/') + INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2) + 1)
        -- Scenario 2: The string only has 2 slashes (ends with string2)
        WHEN (LENGTH(target_column) - LENGTH(REPLACE(target_column, '/', ''))) = 2 THEN
            target_column || '/' || SUBSTR(target_column, INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2) + 1)
        -- Scenario 3: Not enough slashes, leave as-is
        ELSE
            target_column
    END AS updated_column
FROM your_table;

To run this in DB Browser:

• Open your database
• Switch to the Execute SQL tab
• Paste the query, replace the table/column names, and click Run (the play button)
• Check the updated_column results to make sure they look like string0 / string1 / string2 / string2clone / string3 ...

第三步：执行批量更新 #

If the test query looks perfect, run this update statement to make the changes permanent. Again, replace your_table and target_column with your actual names:

UPDATE your_table
SET target_column = 
    CASE 
        WHEN (LENGTH(target_column) - LENGTH(REPLACE(target_column, '/', ''))) >= 3 THEN
            SUBSTR(target_column, 1, INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2) + INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2)) 
            || '/' || SUBSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2) + 1)
            || SUBSTR(target_column, INSTR(SUBSTR(target_column, 1, INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2) + 1), '/') + INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2) + 1)
        WHEN (LENGTH(target_column) - LENGTH(REPLACE(target_column, '/', ''))) = 2 THEN
            target_column || '/' || SUBSTR(target_column, INSTR(SUBSTR(target_column, 1, INSTR(target_column, '/') + 1), '/', 1, 2) + 1)
        ELSE
            target_column
    END
-- Only update rows that have at least 2 slashes (so they have a string2)
WHERE (LENGTH(target_column) - LENGTH(REPLACE(target_column, '/', ''))) >= 2;

After running this, switch to the Browse Data tab to confirm all rows are updated correctly.

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