RegEx Problem

[MostafaBouzari]

Hello everyone,

I'm trying to get a part of information out of the file path

File path is like this 

blablablablabal\A12_2015.xlsx|||`01_2015$`

want to get this part

01_2015

 so my Regex is 

\`\d{2}\_\d{4}\$\`

but it's not working and it won't give me the resualt that i want (It's returns NULL)

How can i achieve this?

[estherb47 · Accepted solution]

Hi @MostafaBouzari 

Which RegEx tool are you using? Regex parse or Regex functions in a formula tool?

In either case, to pull something out of an expression, you should mark what you want returned in parentheses.

Alternatively, you can try the FileGetFileName function to pull out most of the string, and then cleanse out the alpha characters from the field.

Let me know if that works.

Cheers!

Esther

[CharlieS · Accepted solution]

Hi @MostafaBouzari 

if you're looking to return on the sheet name from that path, the string functions offer a non-RegEx alternative.

Trim(Substring([FullPath],FindString([FullPath],"|")),"|$`")

The Substring( function will return the remainder of the string that occurs after the "|" character, which leaves "|||'01_2015$'". This can then be wrapped with a Trim( function to remove the unwanted characters ("|$`").

Check out the attached workflow to see this in action.

[benakesh]

@MostafaBouzari ,

You can try online  before using RegEx in Alteryx .

https://regex101.com/r/Yxxte9/1

[danilang · Accepted solution]

Hi @MostafaBouzari 

The Alteryx Regex parser seems to be having problems with \`.  If you remove the escapes from before both apostrophes, the expression works. 

`(\d{2}_\d{4})\$`

Include the capturing group as @estherb47 suggested, of course.

It's strange because \' works on Regex101.com but not in Alteryx.

Dan 

[estherb47]

The discrepancy with RegEx101 could be the Perl regex, as opposed to other
flavors.

Great catch, @danilang!!

--
Esther Bezborodko
*Senior Manager*
201.650.7314 | estherbezborodko@gmail.com
beautycounter.com/estherbezborodko

*Our mission is to get safe products in the hands of everyone.*
[image: Facebook]

[MostafaBouzari]

Thank you so much @estherb47 

Didn't know that i should use parenthesis 

but i have another problem regarding checking if this is the correct RegEx,Cause it works in RegEx101 but not in alteryx

E.g: alteryx seems to not recognize \´  as @danilang  mentioned 

Is there sth that we should consider when we want to test a Regex in regex101.com?

[MarqueeCrew]

Hmmm, is this a regex challenge?

FileGetExt(Path): Returns the extension of the path, including the . (period).

Replace(ReplaceChar(FileGetExt([FileName]),"'.|",''),"xlsx",'')

i didn't test this reply but think that it will run 3x as fast as the regex. 

cheers,

 Mark

[danilang]

Hi All

As @MarqueeCrew said, 

i didn't test this reply but think that it will run 3x as fast as the regex. 

I've always thought this as well, because of the complex way that Regex scans and parses strings.  So I did test it and the results are interesting.  1M, 10M and 100M rows using the methods from @MarqueeCrew(FileGetExt), @CharlieS(Substring) and myself(Regex)

As Mark conjectured, the native string functions used in Charlie's solution do run ~3 times faster than my Regex based solution.  But what's happening with Mark's method.  It's avoiding Regex, using the FileGetExt() function, but it's run time is on par with the Regex sol'n and 3 times slower than the String based formula.   Strange!  Of course, this could be explained if FileGetExt() uses Regex internally to perform its magic.

Dan