SQLite实现分割字符串

SQLite 官方递归示例

1
2
3
4
5
6
7
8
9
with recursive
    cnt (x) as (
        select 1
        union all
        select x+1
        from cnt
        limit 10000
    )
select x from cnt;

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
drop table if exists COMPANY;

CREATE TABLE COMPANY(
    ID INT PRIMARY KEY     NOT NULL,
    NAME           TEXT    NOT NULL
);


INSERT INTO COMPANY (ID, NAME)
VALUES (1, 'Paul,Allen');

INSERT INTO COMPANY (ID, NAME)
VALUES (2, 'Teddy,Mark,David');

INSERT INTO COMPANY (ID, NAME)
VALUES (3, 'Kim');

使用递归实现分割字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
with recursive
    split (
        ID,
        NAME_splited,
        NAME
    ) as (
        select
            ID,
            '',
            NAME
        from COMPANY
        union all
        select
            ID,
            substr(NAME, 1, case instr(NAME, ',') when 0 then length(NAME) + 1 else instr(NAME, ',') end - 1),
            substr(NAME, case instr(NAME, ',') when 0 then length(NAME) else instr(NAME, ',') end + 1)
        from split
        where NAME <> ''
    )
select
    ID,
    NAME_splited
from split
where NAME_splited <> ''
order by ID
;

结果

ID NAME_splited
1 Paul
1 Allen
2 Teddy
2 Mark
2 David
3 Kim