I would like to replace all pipes and line breaks with space in a free text field in my data base.
My current approach looks like the following:
REGEXP_REPLACE(REGEXP_REPLACE(FREETEXT,'|',‘ ‘),‘\n',' ')
The pipe symbol is a special character in a Regular Expression, splitting it into multiple alternatives, thus you must escape it.
If you want to replace all pipe and line break characters you don't have to nest:
RegExp_Replace(FREETEXT,'[\|\n\r]',' ') \| pipe 0x7C \n line feed 0x0A \r carriage return 0x0D
But as those are single characters you can simply use
OTranslate(FREETEXT, '7C0A0D'xc,' ')
Only if you want to replace consecutive occurences of those characters with a single space you need a RegEx: