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!
