在原生SQLite中从含路径与文件名的字符串提取二者

假设你的表名为files，存储完整路径的字段为full_path，以下是两种直接在SQLite中实现的方案：

方案一：无需自定义函数（通用兼容） #

利用递归CTE（公共表表达式）遍历路径中的反斜杠，定位最后一个反斜杠的位置，进而拆分路径：

WITH RECURSIVE path_parts AS (
  SELECT
    full_path,
    instr(full_path, '\') AS slash_pos
  FROM files
  UNION ALL
  SELECT
    full_path,
    instr(substr(full_path, slash_pos + 1), '\') + slash_pos
  FROM path_parts
  WHERE slash_pos > 0
)
SELECT
  full_path,
  -- 提取父路径：从开头到最后一个反斜杠的位置
  CASE WHEN max(slash_pos) > 0 THEN substr(full_path, 1, max(slash_pos)) ELSE '' END AS parent_path,
  -- 提取文件名：从最后一个反斜杠之后到结尾
  substr(full_path, max(slash_pos) + 1) AS filename
FROM path_parts
GROUP BY full_path;

说明 #

• 递归CTE会反复查找路径中的下一个反斜杠，直到找不到为止，最终通过max(slash_pos)获取最后一个反斜杠的位置
• 处理边界情况：如果路径中没有反斜杠（仅文件名），parent_path会返回空字符串，filename返回完整路径本身
• 示例：对于路径C:\Users\Public\My Music\Classic Queen\16 - Who Wants to Live Forever.mp3，查询后parent_path为C:\Users\Public\My Music\Classic Queen\，filename为16 - Who Wants to Live Forever.mp3

方案二：自定义REVERSE函数（更简洁） #

如果你的SQLite环境允许自定义函数（比如通过应用程序扩展或SQLite命令行加载自定义函数），可以先定义REVERSE函数，再用更直观的方式拆分：

1. 定义REVERSE函数（以SQLite命令行为例） #

CREATE FUNCTION REVERSE(str TEXT) RETURNS TEXT
AS $$
  return str[::-1]
$$ LANGUAGE python;

注：需要SQLite启用Python扩展支持，不同环境的自定义方式可能不同

2. 拆分路径的查询语句 #

SELECT
  full_path,
  -- 父路径
  substr(full_path, 1, length(full_path) - instr(reverse(full_path), '\')) AS parent_path,
  -- 文件名
  substr(full_path, length(full_path) - instr(reverse(full_path), '\') + 2) AS filename
FROM files;

说明 #

• 通过REVERSE反转路径后，用instr找到第一个反斜杠的位置，对应原路径中最后一个反斜杠的位置
• 计算逻辑更简洁，适合支持自定义函数的场景

内容的提问来源于stack exchange，提问作者Jason Blue