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¶

In [1]:

%defaultDatasource jdbc:sqlite:split.sqlite3

In [2]:

create table states (
    state text,
    cities text
)

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

In [3]:

select count(*) from states

Out[3]:

In [6]:

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

In [4]:

select * from states

In [5]:

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 = '\';

In [7]:

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
;