Wildcard in middle of string

Question

Hiya,

I've been looking across multiple threads but couldn't find anything exactly what I needed.  I know that I can use things like CONTAINS() and REGEX_MATCH() to find specific characters within strings, akin to something like:

LIKE '%HELLO_THERE%'  in SQL, using the % as wildcard for anything that comes before or after my characters.  I could write CONTAINS([Field],'HELLO_THERE') and get that result in Alteryx.

My question comes about with Wildcards in the middle of the string - in SQL it could look like:

LIKE '%HELLO%THERE%', and then it will return anything where the word HELLO precedes the word THERE, regardless of any characters before, between, and after it.  Is there anything within Alteryx that would achieve this?  I've tried a combination of formulas / values and can't seem to work it out.

AndyBate · Accepted Answer

Hi,

Apologies if I've misunderstood but based on what you've said, i think the RegexMatch would still work well for you.

However you would replace the underscore with .* as this states any character can be between the two words. Example formula below.

REGEX_Match(sql, '%HELLO.*THERE%')

hope this helps

Qiu · Accepted Answer

@smixon

Based on the idea of @AndyBate , but replace the % at the beginning and end the RegEx and use RegEx Tool to parse.