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.