mysql - SQLITE - 匹配数字并生成具有单个值的另一行

Question

作为 CSV 数据的示例，我有一行:

1;75353;CWB;114#389#115#381#11#382#117#78#118#384;1244;13;4727;15

我想要得到这样的东西:

1;75353;CWB;114;1244;13;4727;15
1;75353;CWB;389;1244;13;4727;15
1;75353;CWB;115;1244;13;4727;15
1;75353;CWB;381;1244;13;4727;15
1;75353;CWB;11;1244;13;4727;15
1;75353;CWB;382;1244;13;4727;15
1;75353;CWB;117;1244;13;4727;15
1;75353;CWB;78;1244;13;4727;15
1;75353;CWB;118;1244;13;4727;15
1;75353;CWB;384;1244;13;4727;15

我尝试过使用(我正在使用 SQLITE，但如果需要的话我也可以使用 MariaDB):

select dt2.zone_number, dt2.section_num, dt1.zone, dt1.section, dt1.local, dt1.local_name, dt1.address, dt1.neighbor
from datatable01 as dt1, datatable02 as dt2 where
(
dt1.zone = dt2.zone_number and
instr(dt2.section_num, dt1.section) > 0
)
order by dt1.zone, dt1.local

但是...这捕获了任何情况。从 datatable02 中，如果 dt1.section = 11，则会从 dt2.section_num 捕获 114(如上例)

我尝试使用regexp '[0-9]{2,3}'，但出现错误(语法错误)。有什么建议可以解决吗？谢谢。

Answer 1

我相信以下内容将满足您的需求，或者可以成为您想要的基础:-

WITH midparts(w, s) AS (
    SELECT '',
            /* extract the mid-part i.e. from after CBW; to the next ; */
            substr(substr(row1,instr(row1,'CWB;')+length('CWB;')),1,instr(substr(row1,instr(row1,'CWB;')+length('CWB;')),';')-1)||'#' 
        FROM t01
    UNION ALL SELECT
    substr(s, 0, instr(s, '#')),
    substr(s, instr(s, '#')+1)
    FROM midparts WHERE s!=''
),
  /* extract the start and end (prefix and suffix) */
    startandend(prefix,suffix) AS (
    SELECT
        substr(row1,1,instr(row1,'CWB;') + length('CWB;') -1), 
        substr(substr(row1,instr(row1,'CWB;')+length('CWB;')),instr(substr(row1,instr(row1,'CWB;')+length('CWB;')),';')) 
    FROM t01
)
SELECT (SELECT prefix FROM startandend)||w||(SELECT suffix FROM startandend) AS generated FROM midparts WHERE w!='';

• 注意到包含数据的表已命名为 t01，包含数据的行为 row1

示例:-

上面的测试使用:-

DROP TABLE IF EXISTS t01;
CREATE TABLE IF NOT EXISTS t01 (row1);
INSERT INTO t01 VALUES('1;75353;CWB;114#389#115#381#11#382#117#78#118#384;1244;13;4727;15');

WITH midparts(w, s) AS (
    SELECT '',
            /* extract the mid-part i.e. from after CBW; to the next ; */
            substr(substr(row1,instr(row1,'CWB;')+length('CWB;')),1,instr(substr(row1,instr(row1,'CWB;')+length('CWB;')),';')-1)||'#' 
        FROM t01
    UNION ALL SELECT
    substr(s, 0, instr(s, '#')),
    substr(s, instr(s, '#')+1)
    FROM midparts WHERE s!=''
),
  /* extract the start and end (prefix and suffix) */
    startandend(prefix,suffix) AS (
    SELECT
        substr(row1,1,instr(row1,'CWB;') + length('CWB;') -1), 
        substr(substr(row1,instr(row1,'CWB;')+length('CWB;')),instr(substr(row1,instr(row1,'CWB;')+length('CWB;')),';')) 
    FROM t01
)
SELECT (SELECT prefix FROM startandend)||w||(SELECT suffix FROM startandend) AS generated FROM midparts WHERE w!='';

结果:-