Replacing characters within a data field

[JC500O]

I have a data contained within a table that consists of a | (pipe) e.g. CAM Supervisor|CAM Users|CAM Group Supervisor

I need to replace the | with a , (comma).

The current formula I'm using: REGEX_Replace([Role], "|", ",") 

Unfortunately the results I receive is as follows: ,C,A,M, ,S,u,p,e,r,v,i,s,o,r,|,C,A,M, ,U,s,e,r,s,|,C,A,M, ,G,r,o,u,p, ,S,u,p,e,r,v,i,s,o,r, which is incorrect

Can someone advise as to what I'm doing wrong, or the correct formula to use.

Many thanks.

[Vijay_M]

Just use this formula: Replace([Role], '|', ",")

-Vijay

[danilang]

Hi @Vijay_M 

The pipe character "|" is a reserved character in REGEX meaning Logical OR.  When you use this without escaping it, it matches every character.  To match the literal "|" character, add a backslash in front of it, like this

REGEX_Replace([Role], "\|", ",")

Then your results will be

CAM Supervisor,CAM Users,CAM Group Supervisor

Dan

[JC500O]

This solved the problem, many thanks

I had not appreciated that the pipe was reserved character and needed to prefix the code with a backslash prior to the pipe e.g. "\|"