hello everyone, i would like to find a Regex pattern within a string, and replace the entire string value with only a portion of that matched pattern
i know i can do this with the following:
REGEX_Replace([Dest], '.*TZ::([/\w]+?),.*', '$1')
But it only works if the pattern exists.. If the pattern isn't found, then the returned value is the entire string (i would prefer it to return null).. a complete solution requires me to first check for the pattern, and THEN replace it
if
REGEX_Match([Dest], '.*TZ::([/\w]+?),.*') = -1
then
REGEX_Replace([Dest], '.*TZ::([/\w]+?),.*', '$1')
else
null()
endif
Is there any way to accomplish this with a single statement without the "If" condition?
Solved! Go to Solution.
@Matthew RegEx works left to right, so the following should work for you:
RegEx_Replace([Field],'.*TZ::([\/\w]+?),.*|.*','$1')
However, as this would return an empty string, you still need a two step process to get what you want.
Personally I think
if
REGEX_Match([Dest], '.*TZ::([/\w]+?),.*') = -1
then
REGEX_Replace([Dest], '.*TZ::([/\w]+?),.*', '$1')
else
null()
endif
Is probably easier to understand than
SWITCH(REGEX_Replace([Field1],'.*TZ::([\/\w]+?),.*|.*','$1'),REGEX_Replace([Field1],'.*TZ::([\/\w]+?),.*|.*','$1'),'',NULL())
Or whatever approach you'd want
Ollie
@OllieClarke hmm, i agree, my original method is probably better for readability.
it just seems cumbersome to need two regex functions just to extract one value from a string.
@Matthew if you want to extract that value, then use the RegEx tool in parse mode
@OllieClarke true, i havent given the regex parse tool much thought
but i have about 25 values that i'm pulling out of a large block of text.. the formula tool seemed like the cleanest way to do it since otherwise i'd need 25 parse tools strung together.. but maybe that isn't so bad
that said, i'm taking another look at your first message.. i think i can live with regex returning the pattern or blank (instead of the pattern or null).. and it requires the least modification, i just need to add "|.*" to each regex pattern
@Matthew Fair enough