如何在SQLite3命令行中将字符串拆分为新列？

debugcn 发表于 Dev

用户名

我正在使用以下.sql脚本在SQLite3中构造一个简单表：

drop table if exists projects;

CREATE TABLE projects(
  "project_number" TEXT,
  "project_manager" TEXT
);

insert into projects ("project_manager", "project_number")
values ("Bob", "11204568-001");
insert into projects ("project_manager", "project_number")
values ("Bill", "11204568-002");
insert into projects ("project_manager", "project_number")
values ("Jack", "11204568-003");
insert into projects ("project_manager", "project_number")
values ("Jill", "11204000");
insert into projects ("project_manager", "project_number")
values ("Fred", "11204569");
insert into projects ("project_manager", "project_number")
values ("Nancy", "11204569-003");

然后，我main_project_number使用以下alter table语句向该表添加一列：

alter table projects add column "main_project_number" integer;

然后，我想仅使用的前缀填充此新列project_number_column，以便得到如下结果：

project_number        project_manager  main_project_number
--------------------  ---------------  -------------------
11204568-001          Bob              11204568
11204568-002          Bill             11204568
11204568-003          Jack             11204568
11204000              Jill             11204000
11204569              Fred             11204569
11204569-003          Nancy            11204569

于是，我尝试过的update一些SQLite的字符串函数语句定义在这里：

update projects set main_project_number = (select substr("project_number", 0, instr("project_number", "-")) from projects);

这产生了以下结果，但是，这不是我想要的...：

project_number        project_manager  main_project_number
--------------------  ---------------  -------------------
11204568-001          Bob              11204568
11204568-002          Bill             11204568
11204568-003          Jack             11204568
11204000              Jill             11204568
11204569              Fred             11204568
11204569-003          Nancy            11204568

我在这里做错了什么？

卢卡斯·索兹达（Lukasz Szozda）

更新应参考特定行：

update projects 
set main_project_number = 
    CASE WHEN instr("project_number", "-") > 0 
         THEN substr("project_number", 0, instr("project_number", "-"))
         ELSE "project_number"
    END

db <> fiddle演示

如果您使用的是SQLite 3.31.0及更高版本，我建议使用生成的列以避免运行更新。

ALTER TABLE projects
ADD COLUMN main_project_number TEXT GENERATED ALWAYS AS
  (CASE WHEN instr("project_number", "-") > 0 
             THEN substr("project_number", 0, instr("project_number", "-"))
             ELSE "project_number"
        END) VIRTUAL;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。