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',' ')
My idea is too replace the pipes | with a space and then the results get checked again and all linebreaks are replaced. Problem now is that there are still pipes in there which messes up the CSV since my delimter for that is |.
Hope anyone can help me out here.
PS: I am not able to change the delimter to something else.
You need to use the occurrence argument, which defaults to 1, meaning replace only the first occurrence. 0 means replace all occurrences.
Also, I believe you can replace all pipes and new-lines at once, which would simplify matters greatly: