community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Case sensitive string comparison

Asteroid

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

Alteryx Certified Partner
Alteryx Certified Partner

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Asteroid

Perfect, works now.

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!

NJ

Alteryx Certified Partner
Alteryx Certified Partner

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

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.

Atom

@nicolejohnson Life changing! Thank you!

Labels