Ben Chuanlong Du's Blog

It is never too late to learn.

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!

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]:
2
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
;

Comments