-- Ordering on case insensitive server
SELECT 'a' AS String
UNION ALL
SELECT 'b'
UNION ALL
SELECT 'A'
UNION ALL
SELECT 'B'
ORDER BY String;
/*
String
------
a
A
b
B
*/
/*
Case sensitive sorting by modifying the collation of the data.
This technique can be used to convert data from one collation to
another also. The COLLATE clause can be used in comparisions also.
*/
SELECT *
FROM (
SELECT 'a'
UNION ALL
SELECT 'b'
UNION ALL
SELECT 'A'
UNION ALL
SELECT 'B'
) AS c( String )
ORDER BY CAST( String as varchar ) COLLATE SQL_Latin1_General_CP850_CS_AS
/*
String
------
A
a
B
b
*/
-- Tip from BOL:
-- To see a list of valid collations, do:
SELECT * FROM ::fn_helpcollations()
-- Sample Output:
/*
name description
------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_Latin1_General_CP850_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 42 on Code Page 850 for non-Unicode Data
SQL_Latin1_General_CP850_CS_AS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 41 on Code Page 850 for non-Unicode Data
SQL_Latin1_General_Pref_CP1_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 53 on Code Page 1252 for non-Unicode Data
SQL_Latin1_General_Pref_CP437_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 33 on Code Page 437 for non-Unicode Data
SQL_Latin1_General_Pref_CP850_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 43 on Code Page 850 for non-Unicode Data
*/
This page was last updated on May 01, 2006 04:28 PM.