How to select sub string in oracle?

Question

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

Chapter 18 Unit 10 Sect 16

• Case 1 : I want to select Chapter 18 from the above string.
• Case 2 : I want to select Unit 10 from the above string.
• Case 3 : I want to select Sect 16 from the above string.

Answer 1

Using 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

Using regexp:

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

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