Menu
I am trying to apply collation on a SELECT DISTINCT statement. anyone now how to do this?
One would think that the DISTINCT would detect upper and lower case as different, i.e.. 'Yes' and 'YES'.
But DISTINCT does not appear to be case sensitive. So I believe I need to add COLLATE...
SELECT DISTINCT COLLATE Latin1_General_CS_AS Shrt_Text AS Sht_text
FROM tblMatStrings
Any idea on how to distinguish upper and lower in a SELECT DISTINCT?
329
GROUP BY is required if you're aggregating data, but in many cases, DISTINCT is simpler to write and read if you aren't aggregating data.
You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation of the database.
A collation allows character data for a given language to be sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, use of symbols or punctuation, character width, and word sorting.
The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
You've just got your syntax a bit backwards
SELECT DISTINCT
Shrt_Text COLLATE Latin1_General_CS_AS As Shrt_text
FROM tblMatStrings
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
