Remove characters between delimiter in Oracle-CodePudding

I'm using Oracle database 18 c and Oracle Apex 22.

I have a string like this one:

'Hello this Marc ||www.stackoverflow.com|| welcome to our family'

I want to remove any character within this delimiter ||

So the output should be:

'Hello this Marc welcome to our family'

Can anyone help with a regexp query that does that?

CodePudding user response：

The way you put it, combination of substr and instr functions returns that result:

Sample data:

SQL> with test (col) as
  2    (select 'Hello this Marc ||www.stackoverflow.com|| welcome to our family' from dual)

Query:

  3  select substr(col, 1, instr(col, '|') - 2) ||
  4         substr(col, instr(col, '|', 1, 4)   1) as result
  5  from test;

RESULT
-------------------------------------
Hello this Marc welcome to our family

SQL>

Or, if you prefer regular expressions, remove everything between outmost pipe signs:

  3  select regexp_replace(col, ' \|. \|') result
  4    from test;

RESULT
-------------------------------------
Hello this Marc welcome to our family

SQL>