join 2 tables case sensitive upper and lower case

Question

I have 2 tables and need to get result on brand code.

In the database i have for example 2 different brands but their code is the same (only difference in lower and upper case). For example:

code Name ab Nike AB Adidas

How to inner join 2 tables on code to get this 2 separately?

Right now after inner join i get total sum of this 2.

SELECT Code, BrandName, Count(*) QTY, SUM(Price) TOTAL
FROM A
INNER JOIN B
ON A.Code=B.Code
GROUP BY Code, BrandName

This query will give me wrong result as it does not join sensitively on upper and lower case.

Please help :)

Defining the database would be a good start to help people help you. But I would assume this is a job for collation. Read up on it. — anothershrubery, Apr 09 '15 at 08:33

jpw · Answer 1 · 2015-04-09T08:39:00.187

7

Since you use a collation that is case insensitive and want to differentiate on case try using the collate keyword with a suitable case-sensitive collation:

INNER JOIN B
ON A.Code COLLATE Latin1_General_CS_AS_KS_WS  = B.Code COLLATE Latin1_General_CS_AS_KS_WS

edited Apr 09 '15 at 08:39

answered Apr 09 '15 at 08:33

jpw

44,361
6
66
86

3

I'm not sure if you need to specify the collation on both sides of the join - it might be enough with `ON A.Code = B.Code COLLATE Latin1_General_CS_AS_KS_WS` – jpw Apr 09 '15 at 08:38
Cool, good to know - I haven't used collate that much before. – jpw Apr 09 '15 at 08:46

score 5 · Accepted Answer · edited May 23 '17 at 12:34

There are at least two quick ways you can solve this.

1. You specify a case-sensitive collation (rules for comparing strings across characters in a character set) for A.Code and B.Code. In MySQL and a few other database management systems, the default collation is case-insensitive.

That is, assuming that you're using MySQL or similar, you'll have to modify your statement as such:

SELECT Code, BrandName, Count(*) QTY, SUM(Price) TOTAL
FROM A
INNER JOIN B
ON A.Code=B.Code COLLATE latin1_bin
GROUP BY Code, BrandName

If, however, you plan on only performing case-sensitive queries on A and B, it may be in your interest to set the default collation on those two tables to case-sensitive.

Please see How can I make SQL case sensitive string comparison on MySQL?

2. Cast A.Code and B.Code to a binary string and compare the two. This is an simple way to compare two strings, byte-by-byte, thus achieving case-insensitivity.

SELECT Code, BrandName, Count(*) QTY, SUM(Price) TOTAL
FROM A
INNER JOIN B
ON BINARY A.Code=B.Code
GROUP BY Code, BrandName

Changing to BINARY is actually a bad idea, as some collations will compare equal for accented characters, for instance, though the binary form would be different. — Joel, Apr 18 '17 at 19:13

score 0 · Answer 3 · answered Jun 10 '21 at 21:25

COLLATE didn't work for me on anything I was trying as the DB was in a utf8 format and wouldn't convert. I was able, however, to use an md5 hash comparison and the md5 function is case-sensitive..

INNER JOIN B ON md5(A.Code) = md5(B.Code)

Note: I don't think this a particularly good solution as it requires a hash calculation on every value every time instead of using stored values, and it has a potential for collision with high row counts. I was using it on a small sample size with a one-off query and it worked well for my situation, so I wanted to throw it out there in case it helps others!

join 2 tables case sensitive upper and lower case

3 Answers3

Linked