|
|
Imagine that we want to group all our customers living in Iceland by their
address.
SELECT count(*) AS cn, [City], [Street]
FROM Customers
WHERE country = 'iceland'
GROUP BY [City], [Street]
|
The result will be like this:
| cn | City | Street |
| 5 | Keflavik | suho |
| 1 | keflavik | SuHo |
This result can not satisfy us, cause it makes difference between characters
in Upper an Low case. This is because COLLATE of the database is Case Sensitive.
A collation specifies the bit patterns that represent each character. It also
specifies the rules that are used to sort and to compare the characters.
Collation string (like this one: SQL_Latin1_General_CP1_CS_AS
) has the following characteristics:
| Language | Codepage | CaseSensitivity | AccentSensitivity |
| Latin1_General | CP1 | CS | AS |
To find collation of the current database run
EXEC sp_helpsort
from Query Analizer.
If existing collation does not satisfy our needs, we can overcome default database
collation inside particular SQL statement:
SELECT count(*) AS cn, [City] COLLATE SQL_Latin1_General_CP1_CI_AS,
[Street] COLLATE SQL_Latin1_General_CP1_CI_AS
FROM Customers
WHERE country = 'iceland'
GROUP BY [City] COLLATE SQL_Latin1_General_CP1_CI_AS, [Street]
COLLATE SQL_Latin1_General_CP1_CI_AS
|
And the result will be like this:
| cn | City | Street |
| 6 | Keflavik | suho |
|