Oracle regexp n characters before and after substring excluding whitespaces

Question

I would like to select 10 characters before and after a particular substring from a string in oracle. If we consider the contents of the table as:

with tmp as (
select 'aa bbbb cccc xx ddddd eeeeeee' as main_text,
'xx' as sub_text
from dual
)
select * from dual;

I want the output to be:
aa bbbb cccc xx ddddd eeeee

So I would like to exclude the spaces and count 10 to the left and 10 to the right of the substring which is 'xx'.

With spaces it is simple, but without spaces in a single query; I am not able to come up with a logic.

Experts who could help please. Using Oracle 11g :D

Answer 1

You can construct a regular expression to do this:

select tmp.*,
       regexp_substr(main_text, '.{0,10}' || sub_text || '.{0,10}')
from tmp;

Note: This doesn't return exactly what you specify, because this counts spaces as a character.

If you don't want to count spaces, you can ignore them as:

select tmp.*,
       regexp_substr(main_text, '([^ ] *){0,10}' || sub_text || '( *[^ ] *){0,10}')
from tmp;

Here is a db<>fiddle.