Micky Maus Micky Maus - 8 months ago 80
SQL Question

How can I replace Pipe (|) with space using regexp_replace in Teradata?

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:


SELECT
ID,
REGEXP_REPLACE(REGEXP_REPLACE(FREETEXT,'|',‘ ‘),‘\n',' ')
FROM TABLE


My idea is to 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.

Answer Source

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:

RegExp_Replace(FREETEXT,'[\|\n\r]+',' ')