Regex Find (return pattern or null)

[Matthew]

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?

[OllieClarke · Accepted 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

[Matthew]

@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.

[OllieClarke]

@Matthew if you want to extract that value, then use the RegEx tool in parse mode

[Matthew]

@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

[OllieClarke]

@Matthew Fair enough