I have a formula in my transform pipeline that contains an instance of the ReplaceRegex() function. It's written like this...

ReplaceRegex(Transaction Description,"^.*(BD\d{3,4}).*$","$1")

If I understand the formula correctly, this should find the substring "BD" followed by three or four digits within a longer string, and then return only the BD and the three or four digits. This should work regardless of how many characters there are before or after the substring. However, if the Transaction Description column is " ABD MOHD SHAHRIR MACBOOK PRO 15"-SPACE GRAY P" then the function returns the whole string. If the Transaction Description column contains a valid BD number, then it returns only the BD substring (this is the desired behaviour).

Perhaps I'm understanding the function incorrectly. If the substring (defined by the regex) isn't found, shouldn't the function return an empty string?

Hi @Jonathan Cutting,

The formula syntax is:

LookupRegex(column_name,match_expression,replacement_expression)

If your regex statement finds a match in the source column the formula will return the value of the capture group.  If no match is found it will return the value of the source column.

For more information, please see ReplaceRegex function.

Thanks,

Gulcin

Thanks, @Gulcin Menekse.

I must have missed that part in the function definition about returning the whole source string. I'll have to figure out a way to prevent this, as I want the result to be an empty string when no match is found.

I'm curious. Do you have any insight on why Apptio designed the function to behave this way? In every other platform, executing a RegEx function will return only the matched strings. It will never return the source string if there are no matches. Apptio is the only platform I know of that exhibits this behaviour.

I will look into this for you @Jonathan Cutting

Hi @Jonathan Cutting,

Like In the example here ReplaceRegex function,  you can add another column to evaluate your regex function column and show things that are not equal to the source column. 

OR, like my colleague @Doug Silk suggested you can work around this in the Regex by having an extra conditional group that always consumes some characters and then returning just the group values you need.

=ReplaceRegex(Transaction Description,"^.*(BD)(\d{3,4})|(.*{3,4}).*$","$1$2")

Please let us know how you get on.. 

Hey, @Gulcin Menekse!

I took a stab at it using your example with multiple replacement expressions in it. Unfortunately, it still returned the entire expression. I did find a workaround, though. I left my RegEx expression alone with the single replacement group, but I wrapped it in an if() function. If the return value of the RegEx was fewer than 7 characters, then it was returned by the if() function. This filtered out the return values that were entire strings, and the only values I saw were legitimate BD numbers.