How to Split Comma-Separated Value in SQLite

Split comma-separated values and map them to original ID in SQLite

After some playing around, I finally figured out the solution myself. It also takes care of rows that have '' or NULL as values for categories:

-- create temporary table which buffers the maximum article ID, because SELECT MAX can take a very long time on huge databases
DROP TABLE IF EXISTS max_article_id;
CREATE TEMP TABLE max_article_id(num INTEGER);
INSERT INTO max_article_id VALUES((SELECT MAX(id) FROM articles));

WITH RECURSIVE split(article_id, word, str, offsep) AS
(
    VALUES ( 0, '', '', 0 )                                      -- begin with dummy article 0 (which does not actually exist) to avoid code duplication
    UNION ALL
    SELECT
        CASE WHEN offsep==0 OR str IS NULL
            THEN article_id+1                                    -- go to next article if the current one is finished
            ELSE article_id                                      -- and keep the current one in the opposite case
        END,
        CASE WHEN offsep==0 OR str IS NULL
            THEN ''
            ELSE substr(str, 0, CASE WHEN instr(str, ',') THEN instr(str, ',') ELSE length(str)+1 END)
        END,
        CASE WHEN offsep==0 OR str IS NULL                       -- when str==NULL, then there has been a NULL value for the categories cell of the current article
            THEN (SELECT categories FROM articles WHERE id=article_id+1)
            ELSE ltrim(substr(str, instr(str, ',')), ',')
        END,
        CASE WHEN offsep==0 OR str IS NULL                       -- offsep==0 means that the splitting was finished in the previous iteration
            THEN 1                                               -- offsep==1 means that splitting the categories for a new article will begin in the next iteration
            ELSE instr(str, ',')                                 -- the actual string splitting stuff is explained and taken from here: http://stackoverflow.com/a/32051164
        END
        FROM split
        WHERE article_id<=(SELECT * FROM max_article_id)         -- stop getting new articles when the maximum article ID is reached
) SELECT article_id, word AS category FROM split WHERE word!=''; -- only select article_id and word from the result to use output the desired table layout

Split values in parts with sqlite

Yes, a recursive common table expression is the solution:

with x(one, firstone, rest) as 
(select one, substr(many, 1, instr(many, ',')-1) as firstone, substr(many, instr(many, ',')+1) as rest from data where many like "%,%"
   UNION ALL
 select one, substr(rest, 1, instr(rest, ',')-1) as firstone, substr(rest, instr(rest, ',')+1) as rest from x    where rest like "%,%" LIMIT 200
)
select one, firstone from x UNION ALL select one, rest from x where rest not like "%,%" 
ORDER by one;

Output:

a|a1
a|a2
a|a3
b|b1
b|b3
c|c2
c|c1

SQLite: Comma Separated Values To New Table & Columns

First create the new table with as many path columns as you need:

CREATE TABLE tablename(
  fileId INTEGER PRIMARY KEY, 
  path0 TEXT, 
  path1 TEXT, 
  path2 TEXT, 
  path3 TEXT, 
  path4 TEXT
);

and then use conditional aggregation to insert rows to the table:

WITH split AS (
  SELECT 0 idx,
         fileId,
         SUBSTR(SUBSTR(Path, 2), 1, INSTR(SUBSTR(Path, 2) || '/', '/') - 1) item,
         SUBSTR(SUBSTR(Path, 2), INSTR(SUBSTR(Path, 2) || '/', '/') + 1) value
  FROM listfile
  UNION ALL
  SELECT idx + 1,
         fileId,
         SUBSTR(value, 1, INSTR(value || '/', '/') - 1),
         SUBSTR(value, INSTR(value || '/', '/') + 1)
  FROM split
  WHERE LENGTH(value) > 0
)
INSERT INTO tablename 
SELECT fileId,
       MAX(CASE WHEN idx = 0 THEN item END),
       MAX(CASE WHEN idx = 1 THEN item END),
       MAX(CASE WHEN idx = 2 THEN item END),
       MAX(CASE WHEN idx = 3 THEN item END),
       MAX(CASE WHEN idx = 4 THEN item END)
FROM split
GROUP BY fileId

I modified the split cte to include the column idx which is used for the order of the path columns.

See the demo.

Results:

|fileId | path0     | path1  | path2  | path3      | path4   
|-----: | :-------- | :----- | :----- | :--------- | :-------
|     1 | video     | gopro  | father | mov001.mp4 | null    
|     2 | pictures  | family | father | Oldman.jpg | null    
|     3 | documents | legal  | father | estate     | will.doc

How to split comma delimited values into multiple rows using Python

Using Python,

cursor.execute("""Select * from Movies""")
all_data = cursor.fetchall()
cursor.execute("""CREATE TABLE IF NOT EXISTS Countries
                    (ID TEXT,
                    Country TEXT)""")
for single_data in all_data:
    countries = single_data[1].split()
    for single_country in countries:
        cursor.execute("""INSERT INTO Countries VALUES(%s,"%s")"""%(single_data[0],single_country))
    conn.commit()

Sqlite: How to split a column by delimiter and order by split count result without excluding duplicates

Read some similar solutions and came up with one based on the ones I read.

Select SomeCol FROM Table Order BY length( replace(SomeCol , ',', '') ) ASC

---

Related Topics