Return multiple keys using REGEXP_SUBSTR

Question

I am trying to read occurrences of text records based on an occurrence. By breaking my head I got the following results:

For example I want to take the second name, using as a basis the key Empr.:

SELECT REGEXP_SUBSTR('empr.: 1234 fulano de tal xpto da silva  empr.: 1235 beltrano de tal silveira   empr.: 1236 fulano de tal xpto da silva' , '[^empr.:]\D+',1,2)  FROM dual; 

I can return all alphabetic characters after the second 'Empr.:':

5 beltrano de tal silveira empr.:

When all I need is:

beltrano de tal silveira

Answer 1

It wasn’t very pretty, but anyway. If you’re using Oracle >= 11g, you can use the sixth parameter of REGEXP_SUBSTR.

---

Assuming the value in question is in the campo of a tabela:

select SUBSTR(s, 1, INSTR(s, ' empr.: ') - 1)
from
  (SELECT
   REGEXP_SUBSTR(campo, '\d+ (.+)', INSTR(campo, 'empr.: ', 1, 2) + 7, 1, '', 1) as s
   FROM tabela); 

INSTR(campo, 'empr.: ', 1, 2) takes the second occurrence of "Empr. :" and returns the position it is in. I use 7 forward positions of this position (so I already skip "Empr. ") and use this as the starting position for REGEXP_SUBSTR. So I can look for \d+ (one or more digits) followed by (.+) - one or more characters, and within parentheses to form a catch group. The sixth parameter indicates that I only want the content of this group, that is, I have already been able to delete the numbers here.

The result of REGEXP_SUBSTR take everything from "beltrano" forward.

Then I use SUBSTR in this result and take everything from the beginning until the next occurrence of "Empr.: ". The result is "beltran of such Ilveira".

See here running in SQL Fiddle.