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:
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:
SELECT SERVERPROPERTY('COLLATION')
SELECT DATABASEPROPERTYEX('Data Warehouse', 'Collation')
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Resource'
AND COLUMN_NAME = 'Name'
What is going wrong with the query? The case sensitivity is disabled as proven before. Why the counts are different?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With