SQLite如何将分号分隔的多值列中所有正数转为负数且不丢失数据

你原来的UPDATE语句之所以只有第一个数值生效，是因为SQLite在对字符串类型的value做数值运算时，会自动从第一个非数字字符处截断取值，12;54运算时只会提取前半段的12，乘-1后得到-12，后半段内容会直接丢失。

方案1：SQLite 3.33.0及以上版本（推荐） #

这个版本开始内置了json相关的表值函数，可以非常方便的拆分合并字符串，直接执行以下SQL即可：

-- 请把your_table_name替换为你实际的表名
UPDATE your_table_name t
SET value = (
    SELECT GROUP_CONCAT('-' || part, ';')
    FROM json_each('["' || REPLACE(t.value, ';', '","') || '"]')
)
WHERE name = 'Julia'
  AND value IS NOT NULL;

实现逻辑 #

1. 先用REPLACE把分号替换为JSON数组的分隔符","，把类似12;54的字符串转成JSON数组格式["12","54"]
2. 用json_each把JSON数组拆分为多行单值
3. 给每个数值前面拼接负号，再用GROUP_CONCAT按分号拼接回原格式

方案2：旧版本SQLite兼容方案 #

如果你的SQLite版本低于3.33.0没有JSON函数，可以用递归CTE实现拆分逻辑：

-- 请把your_table_name替换为你实际的表名
WITH RECURSIVE split_parts(row_id, part, remaining) AS (
    SELECT 
        rowid,
        SUBSTR(value, 1, INSTR(value || ';', ';') - 1),
        SUBSTR(value, INSTR(value || ';', ';') + 1)
    FROM your_table_name
    WHERE name = 'Julia' AND value IS NOT NULL
    UNION ALL
    SELECT 
        row_id,
        SUBSTR(remaining, 1, INSTR(remaining || ';', ';') - 1),
        SUBSTR(remaining, INSTR(remaining || ';', ';') + 1)
    FROM split_parts
    WHERE remaining != ''
)
UPDATE your_table_name t
SET value = (
    SELECT GROUP_CONCAT('-' || part, ';')
    FROM split_parts
    WHERE split_parts.row_id = t.rowid
)
WHERE name = 'Julia' AND value IS NOT NULL;

• 如果你实际的value列中本身存在负数，可以把拼接逻辑里的'-' || part改成'-' || LTRIM(part, '-')，避免出现--12的错误格式
• 建议执行UPDATE前先执行SELECT语句验证结果是否符合预期，避免误改数据：

-- 验证语句，请替换your_table_name
SELECT 
    value AS original_value,
    (
        SELECT GROUP_CONCAT('-' || part, ';')
        FROM json_each('["' || REPLACE(value, ';', '","') || '"]')
    ) AS expected_value
FROM your_table_name
WHERE name = 'Julia' AND value IS NOT NULL;

内容的提问来源于stack exchange，提问作者Horst-Jackson