CODEWITHSUNDEEP
sqlteradataregexp-replace
Micky Maus
Micky Maus

Reputation: 15

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.

Upvotes: 1

Views: 6219

Answers (1)

dnoeth
dnoeth

Reputation: 60472

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]+',' ')

Upvotes: 1

Related Questions