Split String into Rows in SQL

Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

** Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement! **

split¶

SELECT
    A.state,
    split.A.value('.', 'VARCHAR(100)') AS String
FROM (
    SELECT 
        state,  
        CAST('<M>' + REPLACE(city, ',', '</M><M>') + '</M>' AS XML) AS string  
    FROM
        TableA
    ) AS A
CROSS APPLY String.nodes ('/M') AS split(a)

SQLite3¶

%defaultDatasource jdbc:sqlite:split.sqlite3

create table states (
    state text,
    cities text
)

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (table states already exists)

select count(*) from states

2

insert into states (
    state,
    cities
) values (
    'Iowa',
    'Ames, Des Moines, Iowa City'
), (
    'Illinois',
    'Chicago, Buffalo Grove'
)

select * from states

WITH RECURSIVE split(s, last, rest) AS (
  VALUES('', '', 'C:\Users\fidel\Desktop\Temp')
  UNION ALL
  SELECT s || substr(rest, 1, 1),
         substr(rest, 1, 1),
         substr(rest, 2)
  FROM split
  WHERE rest <> ''
)
SELECT s
FROM split
WHERE rest = ''
   OR last = '\';

WITH RECURSIVE split(s, last, rest) AS (
    VALUES('', '', 'C:\Users\fidel\Desktop\Temp')
    UNION ALL
    SELECT s || substr(rest, 1, 1),
         substr(rest, 1, 1),
         substr(rest, 2)
    FROM split
    WHERE rest <> ''
)
SELECT 
    *
FROM 
    split
;

References¶

http://sqljason.com/2010/05/converting-single-comma-separated-row.html

http://www.samuelbosch.com/2018/02/split-into-rows-sqlite.html

https://gist.github.com/dannguyen/1ff581b1e76fddb72cf2ff1542aa6c62

https://stackoverflow.com/questions/34659643/split-a-string-into-rows-using-pure-sqlite