How to split comma-separated value in SQLite?

by

You can use a Common Table Expression to split comma separated values in SQLite.

WITH split(word, csv) AS (
  -- 'initial query' (see SQLite docs linked above)
  SELECT 
    -- in final WHERE, we filter raw csv (1st row) and terminal ',' (last row)
    '', 
    -- here you can SELECT FROM e.g. another table: col_name||',' FROM X
    'Auto,A,1234444'||',' -- terminate with ',' indicating csv ending
  -- 'recursive query'
  UNION ALL SELECT
    substr(csv, 0, instr(csv, ',')), -- each word contains text up to next ','
    substr(csv, instr(csv, ',') + 1) -- next recursion parses csv after this ','
  FROM split -- recurse
  WHERE csv != '' -- break recursion once no more csv words exist
) SELECT word FROM split 
WHERE word!=''; -- filter out 1st/last rows

Output is as expected:

Auto
A
1234444

More Related Contents:

  1. Best way to work with dates in Android SQLite [closed]
  2. Android SQLite: Update Statement
  3. Could not allocate CursorWindow
  4. android, how to exec a sql file in sqlitedatabase
  5. Queries with prepared statements in Android?
  6. sqlite insert into table select * from
  7. SQLite Foreign Key
  8. Unable to save and retrieve String data using Sqlite in android
  9. Share SQLite database between 2 android apps?
  10. Android: upgrading DB version and adding new table
  11. SQLite in Android How to update a specific row
  12. How to disable the Clear Data button in Application info of Manage application
  13. How to convert image file data in a byte array to a Bitmap?
  14. Store Android SQLite
  15. Why do I get a “sqlite3: not found” error on a rooted Nexus One when I try to open a database using the adb shell?
  16. Android sqlite how to check if a record exists
  17. Android Persistence room: “Cannot figure out how to read this field from a cursor”
  18. How to retrieve data from sqlite database in android and display it in TextView
  19. SQLite Exception no such column when trying to select
  20. android java.lang.IllegalStateException: Couldn’t read row 0, col 0 from CursorWindow
  21. How can I insert image in a sqlite database
  22. What is The use of moveToFirst () in SQLite Cursors
  23. Android SQLite auto increment
  24. Android Device Monitor “data” folder is empty
  25. How to check database on not rooted android device
  26. How to store JSON object in SQLite database
  27. sqlite3: not found
  28. Android SQLiteOpenHelper cannot open database file
  29. Getting the type of a column in SQLite
  30. Android table creation Failure (near “autoincrement”: syntax error)?

Leave a Comment