Case sensitivity

Question & Answer
,
1

I’m having an issue on case sensitivity on data.

  1. When I add a filter on a varchar ID looks like lower and upper case are treated/computed the same, so my analysis ends up with more data
  2. when I use the same varchar ID on a Rank function the result is inaccurate since the ID is not treated as unique.

Please let me know.

Thanks,
Benny

2

Have you tried casting it into all lower case?

3

Max, the issue is the IDs are treated as not case sensitive e.g. 1A234b is treated the same as 1a234B. When you use this ID in a Rank function it will partition both IDs as one. Thanks!-Benny

4

For you rank function which one would you like to be first between these two?

5

I believe if the data will be sorted the first one will come first but it should not matter right? Or you’re thinking of some work around? Please share. Thanks!

6

Yeah I’m trying to think of a work around. It would probably be very complex.

Have you looked into casting the id during sql to a number?

Something like this ^

1 Like
7

@Max yeah just verified with our DB guy and he confirmed our RDS Aurora has this column unique otherwise it will not populate uniquely.

any other thoughts, why this is being treated as case insensitive why and we have this situation? I read in the thread of Case Sensitivity that their default scenario should be what’s expected to happen on our environment but it’s the other way around.

thanks!

8

I would suggest opening up a case with AWS.

Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf. Hope this helps!

1 Like
9

Good morning!

I have an update on this, our DB guy went and double check the setting in Aurora MySQL and found out that by default it’s set as case INsensitive. Flipping the switch took care of this issue.

Thanks!

1 Like
10

@bennygene Thank you for providing this solution!