Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Case sensitivity goes crazy

I have a database and I am trying to execute the following query:

SELECT COUNT(*) FROM [Resource] WHERE Name LIKE 'ChinaApp%'

SELECT COUNT(*) FROM [Resource] WHERE Name LIKE 'Chinaapp%'

This is returning 2 different counts:

Counts

The first thing that came to my mind is to check the case sensitivity. I checked the collation on the server level, the database level and the column level:

Server level : Latin1_General_CI_AS

SELECT SERVERPROPERTY('COLLATION')

Server level

Database level : Danish_Norwegian_CI_AS

SELECT DATABASEPROPERTYEX('Data Warehouse', 'Collation')

Database level

Column level : Danish_Norwegian_CI_AS

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Resource'
AND COLUMN_NAME = 'Name'

Column level

Question :

What is going wrong with the query? The case sensitivity is disabled as proven before. Why the counts are different?

like image 463
Moslem Ben Dhaou Avatar asked Oct 21 '22 00:10

Moslem Ben Dhaou


1 Answers

Danish_Norwegian_CI_AS is the issue! Thank you @realspirituals for the hint!

In this default collation I have, 'aa' is actually one single character. The last line in the following link explain it. Å, å, AA, Aa and aa are all the same.

Collation chart for Danish_Norwegian_CI_AS

The following queries now provide the correct result set (count):

SELECT COUNT(*) FROM [Resource] WHERE Name LIKE 'ChinaApp%'

and

SELECT COUNT(*) FROM [Resource] WHERE Name LIKE 'Chinaapp%'
COLLATE Latin1_General_CI_AS
like image 59
Moslem Ben Dhaou Avatar answered Oct 27 '22 09:10

Moslem Ben Dhaou