如何用SQL直接在SQLite表中拆分列生成新列并处理空值？

我有一个SQLite表，其中code列的内容包含用.或-（或两者同时）分隔的编码，示例数据如下：

希望生成一个new_code列，仅保留code的第一部分，示例结果如下：

我已经用Python代码实现了这个逻辑：

def get_column(src, table, column):
    col = src.execute('SELECT %s FROM %s' % (column, table)).fetchall()
    col = ['None' if v is None else v for v in col] # replace nonetypes with string
    col = list(set(col))
    col = [x.split('-', 1)[0] for x in col]

    return col

但想知道是否可以直接用SQL命令完成该操作，且能处理空值（None类型）。

解决方案 #

1. 查询时临时生成new_code（不修改表结构） #

可以结合INSTR、MIN和SUBSTR函数实现需求，同时处理空值：

SELECT 
    code,
    CASE
        WHEN code IS NULL THEN NULL
        ELSE SUBSTR(
            code,
            1,
            MIN(
                INSTR(code, '.') - 1,
                INSTR(code, '-') - 1
            )
        )
    END AS new_code
FROM your_table;

• 逻辑说明：INSTR返回分隔符第一次出现的位置，MIN(INSTR(code,'.')-1, INSTR(code,'-')-1)取第一个出现的分隔符的前一位，以此作为截取终点；
• 无分隔符场景：如果code中没有.和-，INSTR返回0，0-1=-1，SUBSTR(code,1,-1)会返回整个字符串，符合预期；
• 空值处理：通过CASE分支直接返回空值，避免错误截取。

2. 给表添加永久列并填充数据 #

如果需要把new_code作为表的永久列，可以执行以下两步：

-- 第一步：添加新列
ALTER TABLE your_table ADD COLUMN new_code TEXT;

-- 第二步：填充数据
UPDATE your_table
SET new_code = CASE
    WHEN code IS NULL THEN NULL
    ELSE SUBSTR(
        code,
        1,
        MIN(
            INSTR(code, '.') - 1,
            INSTR(code, '-') - 1
        )
    )
END;

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