How To Split Delimited Values In A SQLite Column Into Multiple Columns

How can I parse comma separated values in Fruit Basket and move them to other columns. For example, I want this Fruit Basket Fruit1 Fruit2 Fruit3 ------------ -------- --

Solution 1:

Got it

def returnFruitName(string, index):
    #Split string and remove white space
    return [x.strip() for x in string.split(',')][index]


cur.create_function("returnFruitName", 2, returnFruitName)

cur.execute("UPDATE t SET Fruit1 = returnFruitName(FruitBasket,0) WHERE FruitBasket IS NOT NULL;")
cur.execute("UPDATE t SET Fruit2 = returnFruitName(FruitBasket,1) WHERE FruitBasket IS NOT NULL;")
cur.execute("UPDATE t SET Fruit3 = returnFruitName(FruitBasket,1) WHERE FruitBasket IS NOT NULL;")

Solution 2:

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.

text = [
    'Apple',
    'Banana, Pear',
    'Lemon, Peach, Apricot'
]

for line in text:
    cols = [c.strip() for c in line.split(',')]
    print(cols)

Should output an array for each string line:

['Apple']
['Banana', 'Pear']
['Lemon', 'Peach', 'Apricot']

edit:

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

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) < 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()

Solution 3:

Check the man page :

man sqlite3 | less +/-csv

Then use

sqlite ... -csv | ...

the output will be quit more easy to parse

Solution 4:

I encountered a simular problem and I developed a solution in pure SQL:

--------------------------------------------------
-- Select fruits split into separate columns 
-- Inspired by this article: https://www.vivekkalyan.com/splitting-comma-seperated-fields-sqliteWITH const AS (SELECT 'name' AS name, 10 AS more)
WITH RECURSIVE split(basket_id, fruitCol, str, fruitColNum) AS ( 
WITH const AS (SELECT ', ' AS delimiter)
    SELECT fruit.ROWID, '', Fruit_basket||delimiter, 0 FROM fruit, const
    UNION ALL SELECT
    basket_id,
    substr(str, 0, instr(str, delimiter)),
    substr(str, instr(str, delimiter) + length(delimiter)),
    fruitColNum+1
    FROM split,const WHERE str!=''
) 
SELECT 
      fruit.Fruit_basket, 
      split1.fruitCol as fr_1,
      split2.fruitCol as fr_2,
      split3.fruitCol as fr_3
      -- ...
FROM fruit 
LEFT JOIN (SELECT * FROM split WHERE fruitColNum=1) as split1 ON fruit.ROWID = split1.basket_id
LEFT JOIN (SELECT * FROM split WHERE fruitColNum=2) as split2 ON fruit.ROWID = split2.basket_id
LEFT JOIN (SELECT * FROM split WHERE fruitColNum=3) as split3 ON fruit.ROWID = split3.basket_id
-- ...
   ;

The result of this SELECT is

Fruit_basket            fr_1    fr_2    fr_3
-------------           -----   -----   -----
Apple                   Apple       
Banana, Pear            Banana  Pear    
Lemon, Peach, Apricot   Lemon   Peach   Apricot