sql - 如何 SQLite 将字符串 '123[45]/67[8]/9[10]' 转换为 '123/67/9'

Question

DDL&DML

create table T (value Text);
insert into T (value) values ('123[45]/67[8]/9[10]'),('333[11]/77[222]/55[3]');

我所期望的

value
123/67/9
333/77/55

在线测试链接：SQLite 3.8 | db<>小提琴

我试过的

我尝试使用修剪或替换，但它不起作用。

标签： sqlsqlite

Answer 1

我能想到的唯一方法是使用递归 CTE：

with cte as (
      select NULL as chr, value as rest, 0 as in_braces, '' as output, value, 0 as lev
      from t
      union all
      select substr(rest, 1, 1),
             substr(rest, 2),
             (case when rest like '[%' then 1
                   when rest like ']%' then 0
                   else in_braces
             end) as in_braces,
             (case when in_braces or rest like '[%' then output
                   else output || substr(rest, 1, 1)
              end),
             value, lev + 1
      from cte
      where rest <> ''
     )
select cte.value, cte.output
from cte join
     (select value, max(lev) as max_lev
      from cte
      group by value
     ) m
     on cte.value = m.value and cte.lev = m.max_lev;

这是一个 db<>fiddle。