How to split a string into a new column in SQLite3 command line?

Question

I am constructing a simple table in SQLite3 using the following .sql script:

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");

I then add a column to this table called main_project_number using the alter table statement:

alter table projects add column "main_project_number" integer;

I would then like to populate this new column with only the prefix of the the project_number_column, such that I get a result like this:

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

So I tried an update statement with some sqlite string functions as defined here:

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

This produced the following result, however, which is not what I want...:

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

What am I doing wrong here?

Lukasz Szozda · Accepted Answer · 2020-08-02T10:16:18.347

1

Update should refer to specific row:

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 demo

If you are using SQLite 3.31.0 and above I suggest to use generated columns to avoid running update.

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;

edited Aug 02 '20 at 10:16

answered Aug 02 '20 at 09:51

Lukasz Szozda

162,964
23
234
275

If generated columns are the preferred sqlite alternative then I would like to go with a generated column. Would you be so kind as to take a look at my attempt? I have added it to my edit to the original question... – user32882 Aug 02 '20 at 10:04
1

Never mind, I was missing a comma... generated column works very well – user32882 Aug 02 '20 at 10:09
If you would like to add the generated column alternative in your above answer I will gladly accept it... – user32882 Aug 02 '20 at 10:10
Hmmm.. I was under the impression that the generated column should only be declared upon table creation... I guess that doesn't have to be the case.... – user32882 Aug 02 '20 at 10:18

forpas · Answer 2 · 2020-08-02T10:01:29.780

1

If the column project_number does not have leading 0s you can do it like this:

update projects 
set main_project_number =  project_number + 0;

See the demo.

edited Aug 02 '20 at 10:01

answered Aug 02 '20 at 09:53

forpas

160,666
10
38
76

1

What will happen if project number contains letters/"." or any non digit character? – Lukasz Szozda Aug 02 '20 at 09:54
The column's name is `project_number` so it is safe to assume that there are not any non-digit chars except `-`. The only problem would be if there are leading 0s which I mention in my answer. – forpas Aug 02 '20 at 09:57
In my dataset there are no non-digit characters except for `-`, so this could indeed be a viable solutaion. However I agree with @Lukasz Szozda that it is a bit of a hacky solution, and may lack generality in certain cases... – user32882 Aug 02 '20 at 10:07
This is not hacky. It is based on your sample data and it is far more efficient. – forpas Aug 02 '20 at 10:12

How to split a string into a new column in SQLite3 command line?

2 Answers2