title: Split data in SQLite column
body: <p>I have a SQLite database that looks similar to this:</p>
  
  <pre><code>----------   ------------   ------------
  | Car    |   | Computer |   | Category |
  ----------   ------------   ------------
  | id     |   | id       |   | id       |
  | make   |   | make     |   | record   |
  | model  |   | price    |   ------------
  | year   |   | cpu      |
  ----------   | weight   |
               ------------
  </code></pre>
  
  <p>The <code>record</code> column in my <code>Category</code> table contains a comma separated list of the table name and id of the items that belong to that Category, so an entry would look like this:
  <code>Car_1,Car_2</code>.</p>
  
  <p>I am trying to split the items in the record on the comma to get each value:</p>
  
  <pre><code>Car_1
  Car_2
  </code></pre>
  
  <p>Then I need to take it one step further and split on the <code>_</code> and return the <code>Car</code> records.</p>
  
  <p>So if I know the <code>Category</code> id, I'm trying to wind up with this in the end:</p>
  
  <pre><code>----------------   ------------------
  | Car          |   | Car            |
  ---------------|   -----------------|
  | id: 1        |   | id: 2          |
  | make: Honda  |   | make: Toyota   |
  | model: Civic |   | model: Corolla |
  | year: 2016   |   | year: 2013     |
  ----------------   ------------------
  </code></pre>
  
  <p>I have had some success on splitting on the comma and getting 2 <code>record</code>s back, but I'm stuck on splitting on the <code>_</code> and making the join to the table in the <code>record</code>.</p>
  
  <p>This is my query so far:</p>
  
  <pre><code>WITH RECURSIVE record(recordhash, data) AS (
    SELECT '', record || ',' FROM Category WHERE id = 1
     UNION ALL
    SELECT
           substr(data, 0, instr(data, ',')),
           substr(data, instr(data, ',') + 1)
      FROM record
     WHERE data != '')
  SELECT recordhash
    FROM record 
   WHERE recordhash != ''
  </code></pre>
  
  <p>This is returning</p>
  
  <pre><code>--------------
  | recordhash |
  --------------
  | Car_1      |
  | Car_2      |
  --------------
  </code></pre>
  
  <p>Any help would be greatly appreciated!</p>