title: How to split a string into a new column in SQLite3 command line?
body: <p>I am constructing a simple table in SQLite3 using the following .sql script:</p>
  <pre><code>drop table if exists projects;
  
  CREATE TABLE projects(
    &quot;project_number&quot; TEXT,
    &quot;project_manager&quot; TEXT
  );
  
  insert into projects (&quot;project_manager&quot;, &quot;project_number&quot;)
  values (&quot;Bob&quot;, &quot;11204568-001&quot;);
  insert into projects (&quot;project_manager&quot;, &quot;project_number&quot;)
  values (&quot;Bill&quot;, &quot;11204568-002&quot;);
  insert into projects (&quot;project_manager&quot;, &quot;project_number&quot;)
  values (&quot;Jack&quot;, &quot;11204568-003&quot;);
  insert into projects (&quot;project_manager&quot;, &quot;project_number&quot;)
  values (&quot;Jill&quot;, &quot;11204000&quot;);
  insert into projects (&quot;project_manager&quot;, &quot;project_number&quot;)
  values (&quot;Fred&quot;, &quot;11204569&quot;);
  insert into projects (&quot;project_manager&quot;, &quot;project_number&quot;)
  values (&quot;Nancy&quot;, &quot;11204569-003&quot;);
  </code></pre>
  <p>I then add a column to this table called <code>main_project_number</code> using the <code>alter table</code> statement:</p>
  <p><code>alter table projects add column &quot;main_project_number&quot;  integer;</code></p>
  <p>I would then like to populate this new column with only the prefix of the the <code>project_number_column</code>, such that I get a result like this:</p>
  <pre><code>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
  </code></pre>
  <p>So I tried an <code>update</code> statement with some sqlite string functions as defined <a href="https://www.sqlitetutorial.net/sqlite-string-functions/" rel="nofollow noreferrer">here</a>:</p>
  <p><code>update projects set main_project_number =  (select substr(&quot;project_number&quot;,  0, instr(&quot;project_number&quot;, &quot;-&quot;)) from projects);</code></p>
  <p>This produced the following result, however, which is not what I want...:</p>
  <pre><code>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
  </code></pre>
  <p>What am I doing wrong here?</p>