Split column into different rows on SQLite recursively using delimiter ","

Question

I have a SQLite table just like this:

the table name is 'surat'

But i want to make id_ayat to be split into different rows using SQLite query, and expected result just like this:

  _id|id_surat|id_ayat
  ---+--------+-------
  3  |   2    |  112
  3  |   2    |  213
  3  |   3    |  19
  3  |   3    |  83
  3  |   3    |  85
  3  |   3    |  102

is that possible? what query that i can use in SQLite format?

Answer 1

With a recursive CTE:

with recursive cte as (
  select _id, id_surat, id_ayat, 
    id_ayat + 0 col 
  from tablename 
  union all 
  select _id, id_surat, trim(substr(id_ayat, length(col) + 2)), 
    trim(substr(id_ayat, length(col) + 2)) + 0 
  from cte  
  where instr(id_ayat, ',')
)
select _id, id_surat, col id_ayat 
from cte
order by _id, id_surat

See the demo.
Results:

| _id | id_surat | id_ayat |
| --- | -------- | ------- |
| 3   | 2        | 112     |
| 3   | 2        | 213     |
| 3   | 3        | 19      |
| 3   | 3        | 83      |
| 3   | 3        | 85      |
| 3   | 3        | 102     |