提问人：user2293950 提问时间：9/10/2014 最后编辑：Alex Myersuser2293950 更新时间：5/7/2019 访问量：204
如何在oracle中选择子字符串？
How to select sub string in oracle?
问：
我有一个场景，我的数据如下所示：
第18章 第10单元 第16节
- 案例 1：我想从上面的字符串中选择第 18 章。
- 案例 2 ： 我想从上面的字符串中选择单元 10。
- 案例 3 ： 我想从上面的字符串中选择第 16 节。
答：
4赞
yamny
9/10/2014
#1
我会使用（文档），以及正确的正则表达式。例如：REGEXP_SUBSTR
select regexp_substr('Chapter 18 Unit 10 Sect 16', 'Chapter \d*') from dual;
--Will return: Chapter 18
select regexp_substr('Chapter 18 Unit 10 Sect 16', 'Unit \d*') from dual;
--Will return: Unit 10
select regexp_substr('Chapter 18 Unit 10 Sect 16', 'Sect \d*') from dual;
--Will return: Sect 16
当然，如果你将字符串存储在表中，那么你只需使用这种查询来获得多个结果：Chapter xx Unit yy Sect zz
select regexp_substr(info_column, 'Chapter \d*') from mytable;
您可以替换为 或\d[0-9][[:digit:]]
评论
2赞
Lalit Kumar B
9/10/2014
正则表达式没有问题，直到遇到 CPU 资源消耗过高的问题。我宁愿使用旧的 SUBSTR 来满足如此简单的要求。
0赞
yamny
9/11/2014
当然，如果所讨论的字符串中的值始终为两位数，我会建议正常。substr
0赞
Lalit Kumar B
9/11/2014
查看我的更新。我已经使用 zaratustra 的测试用例证明了 REGEXP 速度较慢。他的测试用例不正确，当更正时，它会显示新的执行时间来证明事实。
2赞
Lalit Kumar B
9/10/2014
#2
我会使用旧的 SUBSTR 而不是 REGEXP。由于 REGEXP 会占用太多的 CPU 资源。
您的要求非常简单。
对于数据，如果您希望第 18 章作为输出：Chapter 18 Unit 10 Sect 16
只需执行：
Select substr(column, 1, 10) from table
同样，您可以对其他输出执行此操作。
编辑：伙计们，你们中的一些人可能会想，为什么我要强调旧的 SUBSTR 而不是 REGEXP。只需打开跟踪，然后查看跟踪输出即可。我同意，在较新的版本中，Oracle 对 REGEXP 进行了大量改进。但是，到目前为止，我还没有看到一个我满意的案例。我可能错了，所以，如果有人有测试用例，我真的很想看看。这对我们所有人来说都是一次很好的学习。
更新以显示上述关于 REGULAR EXPRESSION 的测试用例比 SUBSTR + INSTR 更快，是错误的！
正则表达式：
SQL> DECLARE
2 l_start NUMBER := dbms_utility.get_cpu_time;
3 BEGIN
4 FOR i IN (WITH t
5 AS (SELECT 'Chapter '
6 || LEVEL
7 || ' Unit '
8 || LEVEL
9 || ' Sect '
10 || LEVEL d
11 FROM dual
12 CONNECT BY ROWNUM < 100000)
13 SELECT Regexp_substr(d, 'Chapter [0-9]*') chapter,
14 Regexp_substr(d, 'Unit [0-9]*') unit,
15 Regexp_substr(d, 'Sect [0-9]*') sect
16 FROM t) LOOP
17 NULL;
18 END LOOP;
19
20 dbms_output.Put_line('time taken by REGULAR EXPRESSION : '
21 || ( dbms_utility.get_cpu_time - l_start )
22 || ' hsec');
23 END;
24
25 /
time taken by REGULAR EXPRESSION : 61 hsec
PL/SQL procedure successfully completed.
子 + INSTR ：
SQL> DECLARE
2 l_start NUMBER := dbms_utility.get_cpu_time;
3 BEGIN
4 FOR i IN (WITH t
5 AS (SELECT 'Chapter '
6 || LEVEL
7 || ' Unit '
8 || LEVEL
9 || ' Sect '
10 || LEVEL d
11 FROM dual
12 CONNECT BY ROWNUM < 100000)
13 SELECT Substr(d, 1, Instr(d, ' ', 1, 2) - 1)
14 chapter,
15 Substr(d, Instr(d, ' ', 1, 2),
16 Instr(d, ' ', 1, 4) - Instr(d,
17 ' ', 1, 2))
18 unit,
19 Substr(d, Instr(d, ' ', 1, 4), Length(d) - Instr(d, ' ', 1,
20 4)
21 + 1)
22 sect
23 FROM t) LOOP
24 NULL;
25 END LOOP;
26
27 dbms_output.Put_line('time taken by SUBSTR + INSTR : '
28 || ( dbms_utility.get_cpu_time - l_start )
29 || ' hsec');
30 END;
31
32 /
time taken by SUBSTR + INSTR : 28 hsec
PL/SQL procedure successfully completed.
因此，可以清楚地看到 SUBSTR + INSTR 花费的时间不到 REGULAR EXPRESSION 的一半。
评论
1赞
Alex Poole
9/10/2014
只要数字始终为两位数，并且元素始终按该顺序排列即可。不确定从问题中是否清楚。（“有点像......”总是有点担心！正则表达式更灵活，但显然可能会有成本。
0赞
neshkeev
9/10/2014
看看我的答案。REGEXP 大大击败了 SUBSTR + INSTR。
0赞
Lalit Kumar B
9/10/2014
@zaratustra，当然，让我看一下并测试一下。
0赞
Lalit Kumar B
9/10/2014
@Alex，我并不讨厌正则表达式。只是我不喜欢它们在批处理或 OLTP 系统上的性能。它们非常耗费资源。
0赞
Maheswaran Ravisankar
9/10/2014
#3
我的版本，在数据长度不固定时很有用。！这可能有点泛泛而谈
最后一个参数 实际返回相应的案例结果！regexp_substr
SELECT level as case ,
regexp_substr('Chapter 180 Unit 10 Sect 16 World 100', '\w* \d*( )*',1,level) as result
FROM dual
CONNECT BY level <= CEIL(regexp_count('Chapter 180 Unit 10 Sect 16 World 100',' ')/2)
结果：
CASE RESULT
---------- ------------------------
1 Chapter 180
2 Unit 10
3 Sect 16
4 World 100
评论
0赞
Maheswaran Ravisankar
9/11/2014
它们可能对 OLTP 事务不友好，但非常灵活！
0赞
Lalit Kumar B
9/11/2014
灵活，是的。资源匮乏，是的：-）
0赞
Maheswaran Ravisankar
9/11/2014
是的，兄弟！通常这些要求可以在DW中。我经常使用它。不在线。我的预言机托管在 ..资源滥用，我们从未听说过 DBA..我们只看到使用:pEXADATA
1赞
Lalit Kumar B
9/11/2014
幸运的家伙，哈哈！无论如何，我唯一担心的是标记为正确的答案正在展示不正确的测试用例。EXADATA
0赞
neshkeev
9/10/2014
#4
使用 substr：
declare
l_start number := DBMS_UTILITY.get_cpu_time;
begin
for i in (
with t as (
select 'Chapter ' || level || ' Unit ' || level || ' Sect ' || level d from dual connect by rownum < 100000
)
select substr(d, 1, instr(d, ' ', 1, 2) - 1) chapter
, substr(d,
instr(d, ' ', 1, 2),
instr(d, ' ', 1, 4) - instr(d, ' ', 1, 2)
) unit
, substr(d,
instr(d, ' ', 1, 4),
length(d) - instr(d, ' ', 1, 4) + 1
) sect
from t
)
loop
null;
end loop;
DBMS_OUTPUT.put_line((DBMS_UTILITY.get_cpu_time - l_start) || ' hsec');
end;
126 hsec
使用正则表达式：
declare
l_start number := DBMS_UTILITY.get_cpu_time;
begin
for i in (
with t as (
select 'Chapter ' || level || ' Unit ' || level || ' Sect ' || level d from dual connect by rownum < 100000
)
select regexp_substr(d, 'Chapter [0-9]*') chapter
, regexp_substr(d, 'Unit [0-9]*') unit
, regexp_substr(d, 'Sect [0-9]*') sect
from t
)
loop
null;
end loop;
DBMS_OUTPUT.put_line((DBMS_UTILITY.get_cpu_time - l_start) || ' hsec');
end;
190 hsec
所以正则表达式的解决方案速度较慢，但它更具可读性，如果我是你，我会使用正则表达式。
评论
0赞
Lalit Kumar B
9/11/2014
这是完全错误的测试用例。您的 REGEXP 查询不使用列“d”，而是硬编码了“第 18 章第 10 单元第 16 节”。只需将其替换为 d 列，看看有什么不同。我将编辑我的帖子以添加您的测试用例，以证明我的说法，即 REGEXP 会更慢并且会消耗更多资源。
0赞
Lalit Kumar B
9/12/2014
在新的更新中，也许您复制粘贴了正则表达式中的子代码，因此两者都是相同的代码。您可能需要重新编辑。
上一个：无法显示 Toast
下一个：PHP 字数数组计数
评论
chapter 45 unit 145 sect 2