How to remove a line with a certain character

Question

I have a text like this

'
blabla
blablab $!TOBEREMOVED
blabla

'

I want to remove every lines having '$!' .

therefore my example become that

'
blabla
blabla

'

I would like to use something like that:

SELECT REGEXP_REPLACE (inhalt,'(' || chr(10) || '.$!.' || chr(10) || ')',''

It doesn't remove the line. The problem is that $ allready means something for regex. Is there a way to delete the lines with '$!' ?

Answer 1

You may use:

SELECT inhalt,
       TRIM(REGEXP_REPLACE(inhalt, '(^|' || chr(10) || ').*\$!.*($|' ||
           chr(10) || ')', chr(10))) AS inhalt_out
FROM yourTable;