Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Replacing characters within a data field

Highlighted
Atom

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.

Highlighted
Asteroid

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

 

-Vijay

Highlighted
Castor
Castor

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

Highlighted
Atom

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

Labels