SQL加载器中的转义管道

Question

我有一个管道分隔的文件，必须通过Oracle中的SQL*Loader加载。

我的控制文件如下：

LOAD DATA
REPLACE
INTO TABLE1
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
ID   "TRIM(:ID)",
TEXT "NVL(TRIM(:TEXT),' ')"
)

数据文件中的TEXT列可以包含带有"TEXT"的文本|"-也就是定界符。

如何接受TEXT列中的管道？

Answer 1

您无法转义分隔符；但是，如果希望第一个管道之前的所有内容都是ID，第一个管道之后的所有内容均是TEXT，则可以将数据文件中的记录视为单个字段，并使用SQL函数对其进行拆分，例如：

LOAD DATA
INFILE ...
REPLACE
INTO TABLE TABLE1
TRAILING NULLCOLS
(
ID   CHAR(4000) "regexp_replace(:ID, '^(.*?)(\|(.*))?$', '\1')",
TEXT EXPRESSION "regexp_replace(:ID, '^(.*?)(\|(.*))?$', '\3')"
)

没有FIELDS子句。

ID最初最多包含4000个字符(这只是一个很大的值，希望可以捕获您所拥有的任何数据(。然后对其应用正则表达式替换；该模式将第一组定义为任何字符(非贪婪(，可选地，后面跟着包括管道的第二组和在该管道之后的零个或多个字符的第三内部组。原始值将替换为组1。

TEXT被定义为EXPRESSION，这意味着它不是直接从文件中获得的；相反，相同的regex模式应用于原始ID值，但现在它被第三个组所取代，该组是第一个管道(如果有(之后的所有内容。

在普通SQL中作为演示的等效方法是：

with data (id) as (
select '123|test 1' from dual
union all
select '234|test 2|with pipe' from dual
union all
select '345|test 3|with|multiple|pipes|' from dual
union all
select null from dual
union all
select '678' from dual
union all
select '789|' from dual
)
select id as original,
regexp_replace(ID, '^(.*?)(|(.*))?$', '1') as id,
regexp_replace(ID, '^(.*?)(|(.*))?$', '3') as text
from data;

它给出：

ORIGINAL                        ID   TEXT                         
------------------------------- ---- ------------------------------
123|test 1                      123  test 1                       
234|test 2|with pipe            234  test 2|with pipe             
345|test 3|with|multiple|pipes| 345  test 3|with|multiple|pipes|  

567                             567                               
678|                            678                               

如果您不需要担心没有第一个管道的记录，或者有第一个管道但后面什么都没有的记录，那么regex可能更简单：

(
ID   CHAR(4000) "regexp_replace(:ID, '^(.*?)\|(.*)$', '\1')",
TEXT EXPRESSION "regexp_replace(:ID, '^(.*?)\|(.*)$', '\2')"
)