Alteryx designer Discussions

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

RegEx Syntax and RegEx_Replace Help

8 - Asteroid

I feel really dumb, but I can't figure out the syntax for regular expressions. I feel like I have to search the community for something close to what I need, but what I really want is a syntax guide with specific use examples for building my expressions so I don't have to spend hours trying to find the right match for what I want to achieve. When I do find the right syntax, I don't always know what it is saying/doing, and I want to -- a guide would be helpful. If there is such a thing, please enlighten me because I am a little lost on this front.

 

I have some messy data I am cleaning up. All of the data is in one column. I can parse out all of the data into columns with a space delimiter if I can clean up the names first. I can think of a few ways to achieve, but I can't figure out the syntax to implement it.

 

Example 1:

Smith Jones,Bob 12345 6,789 001 $5,864 3232 6595 LN 15,423 2/7/2003

 

In Example 1 I need to get rid of the space in between Smith and Jones before I can do text to columns using a space delimiter. My thought was that I could do something like RegEx_Replace([Fieldname],"^(* *,)",""). What I am trying to say here is, if there is a space in between words before the first comma, replace it with nothing (remove it). That way no space remains and I can use the text to columns tool to parse the rest of the data. I assumed asterisk was a wildcard, though I read somewhere in the community that maybe "." is the wildcard. I tried it both ways with no luck. I was going to use "Left" but the number of characters isn't consistent.

 

Example 2:

Smith, Bob W. 12345 6789 0001 5864 3232 6595 LN 15,423

 

In Example 2 I need to get rid of the space in between Bob and W. I don't have a plan here. I believe if the first space after the comma is followed by alpha, then get rid of the space. I can't find the syntax to identify alphas.

 

Any guidance on how to solve my big picture problem (a syntax guide) and how to address my specific examples will be greatly appreciated.

Thanks!

Highlighted
11 - Bolide

@JBO,

 

I don't know if you have checked out the actual regex tool, but it has somewhat of a guide(below). As you can see the very bottom option will give you the full list.

 

Regex Capture.PNG

Hope his helps,

MSalvage

Highlighted
8 - Asteroid

Thanks. It does and it doesn't. I don't know what any of the options mean in the replacement dropdown. I also don't know if there is anything special I need to do to string the expressions together or do I just click all that apply for my string.

Highlighted
11 - Bolide

@JBO,

 

You can click on the Regex tool on the tool bar and open the example. It will give you a bettter idea of how the replace functionality works.

 

Also, I have attached a sample workflow solution to your 2 examples. Again, hope this helps.

 

Best,
MSalvage

 

 

Highlighted
8 - Asteroid

Thank you!!

Highlighted

https://regex101.com/ is a great tool for Regular Expressions in general.  As far as using the methods, I'm struggling with the syntax as well.  

Labels