topic Case sensitive string comparison in Alteryx Designer Desktop Discussions

Case sensitive string comparison

jason_scarlett — Thu, 24 May 2018 03:48:37 GMT

I am comparing two string fields in a Filter tool and getting unexpected results.

When I use the Filter tool to compare two strings with values of "Extendicare Fort MacLeod" and "Extendicare Fort Macleod" (note the lowercase "L" in the second), the tool returns these as being equal. i.e. [FAC_NAME]==[FAC_NAME_BAD] returns true.

How do I do a string comparison that is Case Sensitive?

Ideas: https://community.alteryx.com/t5/Alteryx-Product-Ideas/Inconsistency-about-case-sensitivity-across-tools/idi-p/28156

Re: Case sensitive string comparison

MarqueeCrew — Thu, 01 Jun 2017 16:03:58 GMT

Wow @jason_scarlett!

I was unaware of that feature. Here's a sure-fire way to compare:

MD5_ASCII([FAC_NAME])==MD5_ASCII([FAC_NAME_BAD])

Cheers,

Mark

Re: Case sensitive string comparison

jason_scarlett — Thu, 01 Jun 2017 16:13:53 GMT

Perfect, works now.

Re: Case sensitive string comparison

NicoleJohnson — Thu, 01 Jun 2017 16:26:58 GMT

One more way to skin the cat, because RegEx is almost always a solution too... :)

REGEX_Match([Field1],[Field2],0)

The 0 after the two fields you're comparing indicates that it needs to be Case Sensitive. Leaving that clause out (or having it equal to 1) lets the match become Case Insensitive.

Cheers!

Re: Case sensitive string comparison

MarqueeCrew — Thu, 01 Jun 2017 16:35:56 GMT

@jason_scarlett,

Please mark @NicoleJohnson as a solution for this post. Her solution is FASTER than mine. I tested hers with 1,000,000 records and RegEx completes the comparison filter in less than 1 second. The conversion to the MD5 hash and evaluation takes roughly 8 seconds.

Cheers,
Mark

Re: Case sensitive string comparison

Di2g10 — Wed, 18 Oct 2017 15:55:45 GMT

I don't think this will work in the more general case. As regex special characters will cause problems for example '(',')' Since they won't be escaped in a standard string. I think the ASCll Solution is more robust.

Please correct me if i am wrong.

Re: Case sensitive string comparison

ejthoms — Wed, 10 Oct 2018 19:04:14 GMT

@nicolejohnson Life changing! Thank you!

Re: Case sensitive string comparison

etechman — Wed, 01 Jul 2020 20:16:29 GMT

I feel like I learned a lot from this one.

Re: Case sensitive string comparison

PaA — Mon, 10 Aug 2020 15:45:45 GMT

@Di2g10 is right.. regex cannot handle special characters... I would suggest Contains as it has switch for case sensitivity too...

Contains([b], [a],0) AND Length([a]) == Length([b])

or

Contains([b], [a],0) AND Contains([a], [b],0)

//to make it more obvious..

btw. MD5 hash cannot guarantee to have 2 different hashes for every 2 inputs, so it can fail and it is terribly unpredictable (not to mention slow).

Re: Case sensitive string comparison

Di2g10 — Mon, 10 Aug 2020 15:59:27 GMT

I agree this is a better solution to the others proposed (outside of Alteryx fixing what is feel is a bug)