如何在oracle中选择子字符串? [英] How to select sub string in oracle?

问题描述

在我的情况下，我的数据如下所示:

I have a scenario where my data is something like below:

第18章第10单元第16节

Chapter 18 Unit 10 Sect 16

• 案例1:我想从上面的字符串中选择第18章.
• 情况2:我想从上述字符串中选择单元10.
• 情况3:我要从上述字符串中选择Sect 16.

推荐答案

使用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

因此，使用regexp的解决方案速度较慢，但​​可读性更高，如果您是我，我会使用regexp.

So the solution with regexp is slower, but it is more readable, if I were you I would use regexp.

这篇关于如何在oracle中选择子字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！