SQLite 3.31.1如何从字母数字字符串提取纯数字？

需求说明 #

现有字符串 '(123) 456-789 (sou_rav)'，需要在SQLite 3.31.1中提取出无特殊字符的纯数字 123456789，输入输出对应关系如下：

尝试过的无效SQL #

select replace(replace(replace('(123) 456-789 (sou_rav)', rtrim('(123) 456-789 (sou_rav)', '1234567890qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM!#$%^&*()_+-=`~[]\/{}|;:,.<>?'),''),'(',''),')','') as a from table_name;

该查询无效的原因是：rtrim() 仅从字符串末尾移除指定字符集的内容，无法过滤字符串中间的空格、括号、横杠，也无法处理末尾的字母部分，因此达不到提取所有数字的目的。

正确实现方案 #

由于SQLite 3.31.1既没有内置正则替换函数（默认REGEXP需自定义），也未引入TRANSLATE()函数（该函数在3.34.0版本才添加），可以通过递归CTE遍历字符串的每个字符，只保留数字部分：

针对单个字符串的查询 #

WITH RECURSIVE extract_numbers(str, idx, result) AS (
  SELECT '(123) 456-789 (sou_rav)', 1, ''
  UNION ALL
  SELECT str, idx + 1, 
         result || CASE WHEN substr(str, idx, 1) BETWEEN '0' AND '9' THEN substr(str, idx, 1) ELSE '' END
  FROM extract_numbers
  WHERE idx <= length(str)
)
SELECT result AS pure_numbers FROM extract_numbers WHERE idx > length(str);

针对表中字段的查询 #

假设表名为table_name，存储目标字符串的字段名为input_str，可使用以下查询批量提取：

WITH RECURSIVE extract_numbers(input_str, idx, result) AS (
  SELECT input_str, 1, ''
  FROM table_name
  UNION ALL
  SELECT input_str, idx + 1, 
         result || CASE WHEN substr(input_str, idx, 1) BETWEEN '0' AND '9' THEN substr(input_str, idx, 1) ELSE '' END
  FROM extract_numbers
  WHERE idx <= length(input_str)
)
SELECT input_str, result AS pure_numbers 
FROM extract_numbers 
WHERE idx > length(input_str);

逻辑说明 #

递归CTE会逐个遍历字符串的每个字符：

1. 初始步骤：设置待处理字符串、起始索引1、空结果字符串
2. 递归步骤：每次取出当前索引的字符，若为数字则追加到结果中，否则跳过
3. 终止条件：当索引超过字符串长度时，返回最终的纯数字结果

内容的提问来源于stack exchange，提问作者Sourav Guha