Replace Pipe (|) within free text field using REGEXP_REPLACE

Database
N/A

Replace Pipe (|) within free text field using REGEXP_REPLACE

Hi all,

 

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 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. 

1 REPLY
Teradata Employee

Re: Replace Pipe (|) within free text field using REGEXP_REPLACE

See http://info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference%2FB035-1145-160K%2Flvm1...

You need to use the occurrence argument, which defaults to 1, meaning replace only the first occurrence. 0 means replace all occurrences.

REGEXP_REPLACE(FREETEXT,'|',‘ ‘,,0)

Also, I believe you can replace all pipes and new-lines at once, which would simplify matters greatly:

REGEXP_REPLACE(FREETEXT,'[|\n]',‘ ‘,,0)