String Concatenation in Regex Replace

[AGilbert]

Edit: .yxzp attached for sample data

Anyone know if string concatenation with captured groups is allowed in the 'replace' argument of Regex Replace? 

[apathetichell]

 You should be able to do do what you are trying to do. If you have sample data - shoot it over.

in your replacement term you should use ` vs '  (file|||`sheet$range` is Alteryx Excel syntax.)

[Raj]

@AGilbert 
give sample input and Expected output
will try to help with the formula

Regards,
Raj
Raj Agrawal - Team Computers | LinkedIn

[dYoast]

@AGilbert :  Try enclosing the entire portion of your replace statement with single quotes.  I think this will make it a single statement instead of breaking it up with the multiple double quotes.

Something like:  '"$1$2" + ToString([MyField]) + ":$2"', or more generic:  'My Intended Statement'

[CoG]

Yes, you can perform string concatenation within the Regex_Replace() function. The issue you are running into is that the parameter that is your 'replace' input is not each individual substring that you are concatenating, but rather the final result of the concatenation.

One simple change may enlighten you to this issue: change [Value] to a letter and watch everything work exactly the way you hoped it would when value was numerical.

Based on your example, the following is the replace string being used after being fully evaluated:

REGEX_Replace([Filepath], "(.*\$)(\u+)\d+:\u+\d+'", "$1$218:$218")

Notice that the named groups being utilized are: $1 and $218 (which does not exist, and thus returns the empty string).

Possible solutions include:

1. Use a temp string "__VALUE__" instead of [Value] Field, then wrap the Regex_Replace() with the standard Replace( ... , "__VALUE__", ToString([Value]))

2. Incorporate [Value] into the [Filepath] field and add that as a new named group: [Filepath] + "::" + ToString([Value])

Not quite as elegant as you wished it was, but for good reason, and a workable solution is not too complicated.

Hope this helps and Happy Solving!

[OllieClarke · Accepted solution]

To add to @CoG 's solution, if you replace your $ with \ then the issue disappears, although I'm not sure why. I'm still not happy with the difference between $1 and \1 to refer to groups, but in situations like this I'm glad we've got options.

REGEX_Replace([Field1],'(.*\$)(\u+)\d+.*', '\1\2'+tostring([value])+':\2'+tostring([value]))

Hope that helps,

Ollie

[CoG]

@OllieClarke 

That's fantastic! I was not aware that we could refer to groups with "\". Very powerful although worth noting that the reason this notation works for this problem is that the "\" takes exactly one character into consideration, and thus only single digit groups will work with this method (which for 99+% of Regex use-cases is sufficient.  

[AGilbert]

Thank you to both @CoG and @OllieClarke for your answers and I've come back numerous times to ponder them. I've accepted Ollie's answer as the solution as it will likely suffice for most users who find this. However, my personal favorite is CoG's placeholder temp string with a Replace() wrapper. 

Well done and thank you again.