Oracle Database, extract string beeing between two other strings

Question

I need a regexp that's combined with regexp_substr() would give me the word being between two other specified words.

Example:

source_string => 'First Middle Last'

    substring varchar2(100);
    substring := regexp_substr(source_string, 'First (.*) Last'); <===

this doesn't work :(.

dbms_output.put_line(substring) ===> output should be: 'Middle'

I know it looks simple and to be honest, at the beginning I thought the same. But now after spending about 3h for searching for a solution I give up...

So, in your example, you are looking for a "word" (however defined) between the very specific words 'First' and 'Last'? What if there are two or three words between 'First' and 'Last'? And can either 'First' or 'Last' appear more than once in the input string? (And if so, what is the desired result from that?) Also: Why is regexp_replace() part of the problem statement, and not part of one possible solution? Do you have to use regular expressions and ignore all solutions that do not use regular expressions? — user5683823
– user5683823, Commented Jan 31, 2019 at 17:09
I'm sure that these three words will appear only once. Omg I just noticed that I wrote regexp_replace when I mentioned regexp_substr... . Im sorry. I don't have to use regular expressions. If you have some better idea how to do it will be more than welcomed :D — Z.Szymon
– Z.Szymon, Commented Jan 31, 2019 at 20:14

Ben · Accepted Answer · 2019-01-31 16:56:25Z

2

It's not working because the literal strings 'First' and 'Last' are being looked for. Assuming that the strings don't all literally begin 'First' you need to find another way to represent them. You've already done this by representing 'Middle' as (.*)

The next point is that you need to extract a sub-expression (the part in parenthesis), this is the 6th parameter of REGEXP_SUBSTR().

If you put these together then the following gives you what you want:

regexp_substr(source_string, '.*\s(.*)\s.*', 1, 1, 'i', 1)

An example of it working:

SQL> select regexp_substr('first middle last', '.*\s(.*)\s.*', 1, 1, 'i', 1)
  2    from dual;

REGEXP
------
middle

You can also use an online regex tester to validate that 'middle' is the only captured group.

Depending on what your actual source strings look like you may not want to search for exactly spaces, but use \W (a non-word character) instead.

If you're expecting exactly three words I'd also anchor your expression to the start and end of the string: ^.*\s(.*)\s.*$

edited Jan 31, 2019 at 16:56

answered Jan 31, 2019 at 16:51

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Z.Szymon Over a year ago

This is exactly what I was looking for! You saved me so much time, thank you! Oh and actually I will have a long text and I will be looking for these exact three words and I will not know only the middle one. My goal is to find out what it is and your example does that! Thank you once again!

Littlefoot · Accepted Answer · 2019-01-31 19:06:54Z

1

If source string always looks the same, i.e. consists of 3 elements (words), then such a simple regular expression does the job:

SQL> with t (str) as
  2    (select 'First Middle Last' from dual)
  3  select regexp_substr(str, '\w+', 1, 2) result from t;

RESULT
------
Middle

SQL>

answered Jan 31, 2019 at 19:06

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Comments

Barbaros Özhan · Accepted Answer · 2019-02-01 06:38:23Z

0

(\S*) pattern might be used with regexp_replace and regexp_substr as in the following way to get the middle word :

with t(str) as
(
 select 'First Middle Last' from dual
)
select regexp_substr(trim(regexp_replace(str, '^(\S*)', '')),'(\S*)') 
    as "Result String"
  from t;

Result String
-------------
Middle

in the first step First, and in the second one Last words are trimmed.

Or, More directly you can figure out by using regexp_replace as

with t(str) as
(
 select 'First Middle Last' from dual
)
select regexp_replace(str,'(.*) (.*) (.*)','\2') 
    as "Result String"
  from t;

Result String
-------------
Middle

edited Feb 1, 2019 at 6:38

answered Jan 31, 2019 at 17:46

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

1 Comment

Z.Szymon Over a year ago

Thank you! Your regexp works well for three words but because I was not specific enough I forgot to mention that the actual text will be much longer and that these three words will be just a part of that. But anyway, great regexp, thank you!

Collectives™ on Stack Overflow

Oracle Database, extract string beeing between two other strings

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related