ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Using a REGEX formula in the In-DB Formula Tool

Katie_K
7 - Meteor

Hi folks, not sure if this is a simple question or not...

 

I use the RegEx tool, but I haven't used a RegEx expression within a Formula Tool much.  I think they are the same thing, as long as you know how to write the formula - is that correct?

 

I want to use RegEx In-DB... but I see that it's not available.  So I was wondering:  if I can write the RegEx formula, and then use it in a In-DB Formula Tool to get around the fact that the RegEx tool isn't available In-DB.... will it work?

 

I will try it in the meantime, but I need to spend time figuring out how to translate what I have in the RegEx tool into a formula... RegEx is not currently my strong suit so thought I'd ask the question in parallel... thanks!!

 

Katie

mpennington
11 - Bolide

@Katie_K , I'm no expert on the in DB tools, so I will leave that to more knowledgable folks than myself, but I'm pretty comfortable translating from the RegEx tool to the formula tool.  If you want to provide a screenshot and/or data sample, I'm betting someone here can translate.  

 

Edit: Also, yes, for the most part, the tool and the formula (outside of in DB) are pretty much the same. But you can't do things like split to rows based on variable numbers of tokens. For that you would need the tool. If you know precisely how many elements you want to extract, the formula tool is relatively consistent, in my experience.

Katie_K
7 - Meteor

Thanks @mpennington appreciate your comments, it sounds like it may work then.  Here's a screen grab of the RegEx tool config, if you (or anyone else!) know how to put this into a normal formula tool I would be grateful 🙂

mpennington
11 - Bolide

@Katie_K You don't really need the backslash before the dash, so these two produce the same results:

REGEX_Replace([Data], '(.*)\-(.*)', '$2')
REGEX_Replace([Data], '(.*)-(.*)', '$2')

Your RegEx is essentially identifying zero or more of any character followed by a dash and zero or more of any character, so if you are trying to identify something more specific, please provide sample data and I can follow up later.  Here is how to extract the second identified group with a formula tool.

 

RegExForm.jpg

Hope it helps.

Katie_K
7 - Meteor

Thanks @mpennington... I couldn't figure out what to do with the $2, this seems to do it.  Cheers 😄

Labels