使用纯SQLite将字符串拆分为行

sql string sqlite split

7133 观看

3回复

3268 作者的声誉

使用SQLite,我想按以下方式拆分字符串。

输入字符串:

C:\Users\fidel\Desktop\Temp

并让查询返回以下

C:\
C:\Users\
C:\Users\fidel\
C:\Users\fidel\Desktop\
C:\Users\fidel\Desktop\Temp

换句话说,我想将文件路径拆分为其组成路径。有没有办法在纯SQLite中做到这一点?

作者: Fidel 的来源 发布者: 2016 年 1 月 7 日

回应 3


9

142332 作者的声誉

决定

这可以通过递归公用表表达式实现

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

(您没有要求合理的方法。)

作者: CL. 发布者: 2016 年 1 月 7 日

6

94946 作者的声誉

递归CTE:

WITH RECURSIVE cte(org, part, rest, pos) AS (
  VALUES('C:\Users\fidel\Desktop\Temp', '','C:\Users\fidel\Desktop\Temp'|| '\', 0)
  UNION ALL
  SELECT org,
         SUBSTR(org,1, pos + INSTR(rest, '\')),
         SUBSTR(rest, INSTR(rest, '\')+1),
         pos + INSTR(rest, '\')
  FROM cte
  WHERE INSTR(rest, '\') > 0                         
)
SELECT *
FROM cte
WHERE pos <> 0
ORDER BY pos; 

SqlFiddleDemo

输出:

╔═════════════════════════════╗
║            part             ║
╠═════════════════════════════╣
║ C:\                         ║
║ C:\Users\                   ║
║ C:\Users\fidel\             ║
║ C:\Users\fidel\Desktop\     ║
║ C:\Users\fidel\Desktop\Temp ║
╚═════════════════════════════╝

这个怎么运作:

org  - original string does not change
part - simply `LEFT` equivalent of original string taking pos number of chars
rest - simply `RIGHT` equivalent, rest of org string
pos  - position of first `\` in the rest 

跟踪:

╔══════════════════════════════╦══════════════════════════════╦════════════════════════════╦═════╗
║             org              ║            part              ║           rest             ║ pos ║
╠══════════════════════════════╬══════════════════════════════╬════════════════════════════╬═════╣
║ C:\Users\fidel\Desktop\Temp  ║ C:\                          ║ Users\fidel\Desktop\Temp\  ║   3 ║
║ C:\Users\fidel\Desktop\Temp  ║ C:\Users\                    ║ fidel\Desktop\Temp\        ║   9 ║
║ C:\Users\fidel\Desktop\Temp  ║ C:\Users\fidel\              ║ Desktop\Temp\              ║  15 ║
║ C:\Users\fidel\Desktop\Temp  ║ C:\Users\fidel\Desktop\      ║ Temp\                      ║  23 ║
║ C:\Users\fidel\Desktop\Temp  ║ C:\Users\fidel\Desktop\Temp  ║                            ║  28 ║
╚══════════════════════════════╩══════════════════════════════╩════════════════════════════╩═════╝
作者: Lukasz Szozda 发布者: 2016 年 1 月 7 日

5

51 作者的声誉

如果要单独搜索值,请使用以下代码:

WITH RECURSIVE split(content, last, rest) AS (
VALUES('', '', 'value1§value2§value3§value4§value5§value6§value7')
UNION ALL
  SELECT 

    CASE WHEN last = '§' 
            THEN
                substr(rest, 1, 1)
            ELSE
                content || substr(rest, 1, 1)
    END,
     substr(rest, 1, 1),
     substr(rest, 2)
  FROM split
  WHERE rest <> ''
)
SELECT 
       REPLACE(content, '§','') AS 'ValueSplit'     
FROM 
       split
WHERE 
       last = '§' OR rest ='';

结果:

**ValueSplit**

value1
value2
value3
value4
value5
value6
value7

我希望我能为遇到同样问题的人提供帮助。

作者: Bruno Matos 发布者: 2016 年 12 月 28 日
32x32