How to split delimited values in a SQLite column into multiple columns
<p>How can I parse comma separated values in <code>Fruit Basket</code> and move them to other columns. </p>

<p>For example, I want this</p>

<pre><code>Fruit Basket  Fruit1    Fruit2    Fruit3
------------  --------  --------  --------
Apple
Banana, Pear
Lemon, Peach, Apricot
</code></pre>

<p>to becomes this</p>

<pre><code>Fruit Basket  Fruit1    Fruit2    Fruit3
------------  --------  --------  --------
Apple         Apple
Banana, Pear  Banana    Pear
Lemon, Pea... Lemon     Peach      Apricot
</code></pre>

<p>If I cannot do this with a pure SQLite statement, how can I do it using Python?</p>

<p>Pulling apart the one column is be pretty simple for Python (not sure about SQLite). This simplifies your DB row into an array of strings and should be similar for the SQLite return.</p>

<pre><code>text = [
    'Apple',
    'Banana, Pear',
    'Lemon, Peach, Apricot'
]

for line in text:
    cols = [c.strip() for c in line.split(',')]
    print(cols)
</code></pre>

<p>Should output an array for each string line:</p>

<pre><code>['Apple']
['Banana', 'Pear']
['Lemon', 'Peach', 'Apricot']
</code></pre>

<p><strong>edit:</strong></p>

<p>Here's a full Python script to do what you're looking for to SQLite:</p>

<pre><code>import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
c.execute(
    '''SELECT *
            FROM Fruits
            WHERE Fruit_Basket IS NOT NULL'''
)
rows = c.fetchall()
for row in rows:
    fruit_basket = row[0]
    fruits = [f.strip() for f in fruit_basket.split(',')]
    while (len(fruits) &lt; 3):
        fruits.append('')
    print(fruits)
    update = '''UPDATE Fruits
                    SET Fruit1 = ?, Fruit2 = ?, Fruit3 = ?
                    WHERE Fruit_Basket = ?'''
    c.execute(update, fruits + [fruit_basket,])
conn.commit()
conn.close()
</code></pre>

python sqlite