在 SQL 中解析伪列化数据的正则表达式

Question

我正在做一个项目，我们试图从FAA发布的重新路由公告中解析信息。这些建议以自由文本形式发布，结构松散，目的是允许查看者将它们打印在一张纸上。

最感兴趣的领域是公告的最后一部分，其中包含与给定改道相关的特定信息 - 始发地和目的地机场以及适用于它们之间任何航班的特定所需航线。下面是一个示例：

ORIG                DEST                ROUTE
----                ---------------     ---------------------------
FRG                 MCO TPA PIE SRQ     WAVEY EMJAY J174 SWL CEBEE 
FMY RSW APF         WETRO DIW AR22 JORAY HILEY4

我想做的是能够将其解析为三个条目，如下所示：

原：西德

DEST： MCO TPA PIE SRQ FMY RSW APF

路线： 波浪 EMJAY J174 SWL CEBEE 韦特罗迪乌 AR22 乔雷·希利4

以下是我当前用于解析这部分公告的三个代码段：

起源

regexp_substr(route_1,'^(([A-Z0-9]|(|)|-)+s)+')

目的地

regexp_substr(route_1,'(([A-Z0-9]|(|)|-)+s)+',1,2) 

路由字符串

regexp_substr(route_1, 's{2,}>?([A-Z0-9]|>|<|s|:)+<?$')

虽然这些表达式可以处理"起点"和"终点"部分都在第一行的大多数情况，但它们无法处理前面提供的示例。有谁知道我如何能够成功解析原始示例中的文本？

Answer 1

概念证明。

输入文件是纯文本文件(没有制表符，只有空格)。垂直上，它分为三列，宽度固定：20 个字符、20 个字符、剩余的任何东西(直到行尾)。

前两行始终填充，带有标题和-----。可以忽略这两行。然后，文件的其余部分(向下读取页面)按 ORIG 列中最新的非空字符串"分组"。

输入文件如下所示：

ORIG                DEST                ROUTE
----                ---------------     ---------------------------
FRG                 MCO TPA PIE SRQ     WAVEY EMJAY J174 SWL CEBEE 
FMY RSW APF         WETRO DIW AR22 JORAY HILEY4
ABC                 SFD RRE BAC         TRIO SBL CRT              
POLDA                                   FARM OLE BID ORDG BALL
BINT LFV
YYT                 PSS TRI BABA        TEN NINE FIVE
COL DMV
SAL                 PRT DUW             PALO VR22 NOL3

请注意块之间的空行，一个块中的空DEST(我处理它，尽管在OP的问题中是不可能的)，以及在某些情况下DEST和ROUTE使用的不同行数。

文件名是inp.txt的，它驻留在我已告知 Oracle 的目录中：create directory sandbox as 'c:appsandbox'。(首先我必须grant create any directory to <myself>，同时以SYS身份登录。

输出如下所示：

ORIG  DEST                        ROUTE                                              
----- --------------------------- ------------------------------------------------------
FRG   MCO TPA PIE SRQ FMY RSW APF WAVEY EMJAY J174 SWL CEBEE WETRO DIW AR22 JORAY HILEY4 
ABC   SFD RRE BAC                 TRIO SBL CRT                                           
POLDA                             FARM OLE BID ORDG BALL BINT LFV                        
YYT   PSS TRI BABA COL DMV        TEN NINE FIVE                                          
SAL   PRT DUW                     PALO VR22 NOL3

我分两步完成了此操作。首先，我创建了一个帮助表，INP，有四列(RN 编号、ORIG varchar2(20)、DEST varchar2(20)、ROUTE varchar2(20))，然后通过一个过程从文本文件中导入。然后我进一步处理了这个问题，并使用输出来填充最终表。这不太可能是最有效的方法(也许有很好的理由不按照我的方式去做);我一般没有UTL_FILE和将文本文件导入 Oracle 的经验。我这样做有两个原因：学习，并证明它是可以做到的。

将文本文件导入帮助程序表的过程：

Create or Replace PROCEDURE read_inp is
f UTL_FILE.FILE_TYPE;
s VARCHAR2(200);
rn number := 1;
BEGIN
f := UTL_FILE.FOPEN('SANDBOX','inp.txt','r', 200);
LOOP
BEGIN
UTL_FILE.GET_LINE(f,s);
INSERT INto inp (rn, orig, dest, route)
VALUES
(rn, trim(substr(s, 1, 20)), trim(substr(s, 21, 20)), trim(substr(s, 41)));
END;
rn := rn + 1;
END LOOP;
exception
when no_data_found then 
utl_file.fclose(f);
END;
/
exec read_inp
/

以及进一步的处理(创建REROUTE表之后)：

create table reroute ( orig varchar2(20), dest varchar2(4000), route varchar2(4000) );
insert into reroute
select max(orig),
trim(listagg(dest , ' ') within group (order by rn)),
trim(listagg(route, ' ') within group (order by rn))
from   ( 
select rn, orig, dest, route, count(orig) over (order by rn) as grp
from   inp
where  rn >= 3
)
group by grp
;