由此产生的数据集只应delimters之前显示的字符串PLE，#并ALL以正确的顺序。程序包中已经存在的“当前查询”是这样的（DDL和DML在帖子的底部）：
PLE
#
ALL
SELECT DATA1 , DECODE(SIGN(0 - instr(DATA1, 'PLE')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'PLE') - 1)) GET_DATA_TILL_FIRST_PLE , DECODE(SIGN(0 - instr(DATA1, '#')), -1, SUBSTR(DATA1, 1, instr(DATA1, '#') - 1)) GET_DATA_TILL_FIRST_NUM_SIGN , DECODE(SIGN(0 - instr(DATA1, 'ALL')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'ALL') - 1)) GET_DATA_TILL_FIRST_ALL , NVL(DECODE(SIGN(0 - instr(DATA1, 'PLE')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'PLE') - 1), 0, DECODE(SIGN(0 - instr(DATA1, '#')), -1, SUBSTR(DATA1, 1, instr(DATA1, '#') - 1), 0, DECODE(SIGN(0 - instr(DATA1, 'ALL')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'ALL') - 1), DATA1), DATA1), DATA1), DATA1) PUT_THEM_ALL_TOGETHER FROM table_x;
哪些结果会生成以下数据集：
DATA1 | GET_DATA_TILL_FIRST_PLE | GET_DATA_TILL_FIRST_#_SIGN | GET_DATA_TILL_FIRST_ALL | PUT_THEM_ALL_TOGETHER ----------------------- | ----------------------- | --------------------------- | -------------------------- | ---------------------- STRING_EXAMPLE | STRING_EXAM | | | STRING_EXAM TREE_OF_APPLES | TREE_OF_AP | | | TREE_OF_AP FIRST_EXAMPLE | FIRST_EXAM | | | FIRST_EXAM IMPLEMENTATION | IM | | | IM PARIS | | | | PARIS PLEONASM | | | | PLEONASM XXXX 1 | | | | XXXX 1 XXXX YYYYYY 2 FFFFFFFFF | | | | XXXX YYYYYY 2 FFFFFFFFF XXXX YYYYYY 5FFFFFFFFF | | | | XXXX YYYYYY 5FFFFFFFFF OPOPOPOPO #09090 APPLE | OPOPOPOPO #09090 AP | OPOPOPOPO | OPOPOPOPO # | OPOPOPOPO #09090 AP OPOPOPOPO BALL# | | OPOPOPOPO BALL | OPOPOPOPO B | OPOPOPOPO BALL BALL IS #LIFE | | BALL IS | B | BALL IS
PS。 我只需要专栏，PUT_THEM_ALL_TOGETHER但我也包括其他专栏，但确实添加了上下文。
PUT_THEM_ALL_TOGETHER
我发现查询有点混乱并且难以阅读，因此我尝试使用REGEXP_SUBSTR@vkp并提出了建议，我想出了以下查询，结果与上面的数据集相同。
REGEXP_SUBSTR
SELECT DATA1 , REGEXP_SUBSTR(DATA1, '(.+?)PLE',1,1,null,1) GET_DATA_TILL_FIRST_PLE , REGEXP_SUBSTR(DATA1, '(.+?)#',1,1,null,1) GET_DATA_TILL_FIRST_#_SIGN , REGEXP_SUBSTR(DATA1, '(.+?)ALL',1,1,null,1) GET_DATA_TILL_FIRST_ALL , COALESCE(REGEXP_SUBSTR(DATA1, '(.+?)PLE',1,1,null,1), REGEXP_SUBSTR(DATA1, '(.+?)#',1,1,null,1), REGEXP_SUBSTR(DATA1, '(.+?)ALL',1,1,null,1), DATA1) PUT_THEM_ALL_TOGETHER FROM table_x;
然而，从@ MathGuy的答案，似乎INSTR和SUBSTR有效得多。我在某种程度上进行了测试，这就是我得到的：
INSTR
SUBSTR
使用INSTR和SUBSTR：
SET TIMING ON; BEGIN UPDATE table_x SET DATA2 = NVL(DECODE(SIGN(0 - instr(DATA1, 'PLE')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'PLE') - 1), 0, DECODE(SIGN(0 - instr(DATA1, '#')), -1, SUBSTR(DATA1, 1, instr(DATA1, '#') - 1), 0, DECODE(SIGN(0 - instr(DATA1, 'ALL')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'ALL') - 1), DATA1), DATA1), DATA1), DATA1); ROLLBACK; END; /
PL / SQL过程成功完成。 播放时间：00：00：00.234
使用REGEXP_SUBSTR：
SET TIMING ON; BEGIN UPDATE table_x SET DATA2 = COALESCE(REGEXP_SUBSTR(DATA1, '(.+?)PLE',1,1,null,1) ,REGEXP_SUBSTR(DATA1, '(.+?)#',1,1,null,1) ,REGEXP_SUBSTR(DATA1, '(.+?)ALL',1,1,null,1) ,DATA1); ROLLBACK; END; /
PL / SQL过程成功完成。 播放时间：00：00：00.236
虽然这是一个非常有限的测试数据，它表明的组合INSTR和SUBSTR一点点的速度比REGEXP_SUBSTR。难道是可以忽略不计在使用REGEXP_SUBSTR替代INSTR和SUBSTR提高可读性的缘故？
DML和DDL：
create table table_x ( data1 varchar2(100) ,data2 varchar2(100) ); INSERT INTO table_x (DATA1) VALUES ('STRING_EXAMPLE'); INSERT INTO table_x (DATA1) VALUES ('TREE_OF_APPLES'); INSERT INTO table_x (DATA1) VALUES ('FIRST_EXAMPLE'); INSERT INTO table_x (DATA1) VALUES ('IMPLEMENTATION'); INSERT INTO table_x (DATA1) VALUES ('PARIS'); INSERT INTO table_x (DATA1) VALUES ('PLEONASM'); INSERT INTO table_x (DATA1) VALUES ('XXXX 1'); INSERT INTO table_x (DATA1) VALUES ('XXXX YYYYYY 2 FFFFFFFFF'); INSERT INTO table_x (DATA1) VALUES ('XXXX YYYYYY 5FFFFFFFFF'); INSERT INTO table_x (DATA1) VALUES ('OPOPOPOPO #09090 APPLE'); INSERT INTO table_x (DATA1) VALUES ('OPOPOPOPO BALL#'); INSERT INTO table_x (DATA1) VALUES ('BALL IS #LIFE');
谢谢你。
我已经张贴出如何解决使用这一问题的答案INSTR，并SUBSTR以正确的方式。
在本“答案”中，我解决了另一个问题-哪种解决方案更有效。我将在下面解释测试，但这是最重要的一点：REGEXP解决方案花费的 时间 比INSTR/SUBSTR解决方案 长40倍 。
REGEXP
INSTR/SUBSTR
设置 ：我创建了一个包含150万个随机字符串的表（所有字符串长度均为8个字符，全部为大写字母）。然后，我修改了10％的字符串以添加子字符串'PLE'，再修改了10％的添加了a '#'，再修改了10％的添加'ALL'。我这样做的分裂在位置上的原始字符串mod(rownum, 9)-这是0和8之间的数字- 和连接'PLE'或'#'或'ALL'在该位置。当然，这不是获取我们所需测试数据的最有效或最优雅的方法，但这无关紧要- 关键是创建测试数据并将其用于我们的测试中。
'PLE'
'#'
'ALL'
mod(rownum, 9)
所以：现在我们有了一个只有一列的表，其中data1有150万行中的一些随机字符串。各10％的子串PLE或#或ALL在其中。
data1
测试包括创建data2与原始帖子中相同的新字符串。我没有将结果插入表中；而是将结果插入表中。无论如何data2计算， 将 其重新 插入 表中的时间都应该相同。
data2
相反，我将主查询放在一个外部查询中，该查询用于计算结果data2值的长度之和。这样，我保证优化器不能采用快捷方式：data2必须生成所有值，必须测量它们的长度，然后将它们求和。
下面是创建基表所需的语句，我将其称为table_z，然后运行了查询。
table_z
create table table_z as select dbms_random.string('U', 8) as data1 from dual connect by level <= 1500000; update table_z set data1 = case when rownum between 1 and 150000 then substr(data1, 1, mod(rownum, 9)) || 'PLE' || substr(data1, mod(rownum, 9) + 1) when rownum between 150001 and 300000 then substr(data1, 1, mod(rownum, 9)) || '#' || substr(data1, mod(rownum, 9) + 1) when rownum between 300001 and 450000 then substr(data1, 1, mod(rownum, 9)) || 'ALL' || substr(data1, mod(rownum, 9) + 1) end where rownum <= 450000; commit;
INSTR/SUBSTR 解决方案
select sum(length(data2)) from ( select data1, case when instr(data1, 'PLE', 2) > 0 then substr(data1, 1, instr(data1, 'PLE', 2) - 1) when instr(data1, '#' , 2) > 0 then substr(data1, 1, instr(data1, '#' , 2) - 1) when instr(data1, 'ALL', 2) > 0 then substr(data1, 1, instr(data1, 'ALL', 2) - 1) else data1 end as data2 from table_z ); SUM(LENGTH(DATA2)) ------------------ 10713352 1 row selected. Elapsed: 00:00:00.73
REGEXP 解决方案
select sum(length(data2)) from ( select data1, COALESCE(REGEXP_SUBSTR(DATA1, '(.+?)PLE',1,1,null,1) ,REGEXP_SUBSTR(DATA1, '(.+?)#',1,1,null,1) ,REGEXP_SUBSTR(DATA1, '(.+?)ALL',1,1,null,1) ,DATA1) as data2 from table_z ); SUM(LENGTH(DATA2)) ------------------ 10713352 1 row selected. Elapsed: 00:00:30.75
在任何人提出这些建议之前，我都重复了两次查询；第一个解决方案的运行时间通常为0.75到0.80秒，第二个查询的运行时间为30到35秒。慢40倍以上。（因此，编译器/优化器花费时间来编译查询不是问题；这实际上是执行时间。）而且，这与从基表中读取150万个值无关，这与两种测试的时间都比处理要少得多。无论如何，我都会先运行INSTR/SUBSTR查询，因此，如果有任何缓存，那么该REGEXP查询将是一个受益的地方。
编辑 ：我只是想出了建议的REGEXP解决方案中的一个低效率。如果我们将搜索模式锚定到字符串的开头（例如'^(.+?)PLE'，注意^锚点），则REGEXP查询的运行时间将从30秒降至10秒。显然，Oracle实现不足以识别这种等效性，并尝试从第二个字符，第三个字符等进行搜索。执行时间仍然要长15倍；15 <40，但仍然相差很大。
'^(.+?)PLE'
^
