SQLite学习笔记-多个分隔符分割字符串并删除后缀

SQLite单个分隔符分割字符串删除后缀实现很简单

SELECT 'aaa-bbb-ccc-ddd-eee', SUBSTR('aaa-bbb-ccc-ddd-eee', 0, LENGTH(RTRIM('aaa-bbb-ccc-ddd-eee', REPLACE('aaa-bbb-ccc-ddd-eee', '-', ''))));

SQLite多个分隔符分割字符串需要借助递归查询实现

多个分隔符分割字符串递归查询过程：i 记录分隔符坐标位置 input 记录下一次递归查询的字符串 origin 记录原始字符串 

当前字符串中无法定位多个分隔符坐标的时候终止递归查询

示例中均是以 '-1' 作为多个分隔符 T_TABLE作为数据库表 COL作为数据库表中一个字段列

WITH SP(i, input, origin) AS (
   SELECT DISTINCT 1, COL, COL FROM T_TABLE WHERE (LENGTH(COL) - LENGTH(REPLACE(COL, '-1', ''))) / LENGTH('-1') > 0
   UNION
   SELECT INSTR(input, '-1') + LENGTH('-1') - 1, SUBSTR(input, INSTR(input, '-1') + LENGTH('-1')), origin FROM SP WHERE INSTR(input, '-1') != 0
) SELECT i, input, origin FROM SP;

聚合计算出最后一个多字符分割的位置，取出位置之前的子字符串

WITH SP(i, input, origin) AS (
   SELECT DISTINCT 1, COL, COL FROM T_TABLE WHERE (LENGTH(COL) - LENGTH(REPLACE(COL, '-1', ''))) / LENGTH('-1') > 0
   UNION
   SELECT INSTR(input, '-1') + LENGTH('-1') - 1, SUBSTR(input, INSTR(input, '-1') + LENGTH('-1')), origin FROM SP WHERE INSTR(input, '-1') != 0
) SELECT SUM(i) AS pos, origin, SUBSTR(origin, 0, SUM(i) - LENGTH('-1')) AS substring FROM SP GROUP BY origin;

SQLite多个分隔符分割字符串并删除后缀

WITH SP(i, input, origin) AS (
   SELECT DISTINCT 1, COL, COL FROM T_TABLE WHERE (LENGTH(COL) - LENGTH(REPLACE(COL, '-1', ''))) / LENGTH('-1') > 0
   UNION
   SELECT INSTR(input, '-1') + LENGTH('-1') - 1, SUBSTR(input, INSTR(input, '-1') + LENGTH('-1')), origin FROM SP WHERE INSTR(input, '-1') != 0
) UPDATE T_TABLE SET COL = (SELECT SUBSTR(b.ORIGIN, 0, b.POS - LENGTH('-1')) FROM (SELECT SUM(i) AS POS, origin AS ORIGIN FROM SP GROUP BY origin) b 
WHERE T_TABLE.COL = b.ORIGIN) WHERE (LENGTH(COL) - LENGTH(REPLACE(COL, '-1', ''))) / LENGTH('-1') > 0;