--This example demonstrates
that a column data can be presented with diffrent collations via CTE
USE MyDB
GO
CREATE TABLE #words
(
word NVARCHAR(20) NOT NULL ,
wordno TINYINT PRIMARY
KEY CLUSTERED
);
INSERT #words
VALUES ( N'cloud', 1 ),
( N'CSAK', 6 ),
( N'cukor', 11 ),
( N'Oblige', 2 ),
( N'Opera', 7 ),
( N'?l', 12 ),
( N'résumé', 3 ),
( N'RESUME', 8 ),
( N'R?SUM?', 13 ),
( N'resume', 4 ),
( N'resumes', 9 ),
( N'résumés', 14 ),
( N'?IL', 5 ),
( N'?il', 10 );
-- Latin1_General_CI_AS
WITH collatedwords ( collatedword,
wordno )
AS ( SELECT word COLLATE Latin1_General_CI_AS ,
wordno
FROM #words
)
SELECT
collatedword ,
rank
= DENSE_RANK() OVER ( ORDER BY collatedword ) ,
wordno
FROM
collatedwords
ORDER BY
collatedword;
-- Latin1_General_CS_AS
WITH collatedwords ( collatedword,
wordno )
AS ( SELECT word COLLATE Latin1_General_CS_AS ,
wordno
FROM #words
)
SELECT
collatedword ,
rank
= DENSE_RANK() OVER ( ORDER BY collatedword ) ,
wordno
FROM
collatedwords
ORDER BY
collatedword;
-- Latin1_General_CI_AI
WITH collatedwords ( collatedword,
wordno )
AS ( SELECT word COLLATE Latin1_General_CI_AI ,
wordno
FROM #words
)
SELECT
collatedword ,
rank
= DENSE_RANK() OVER ( ORDER BY collatedword ) ,
wordno
FROM
collatedwords
ORDER BY
collatedword;
--Hungarian_CI_AI
/* Note: The words CSAK and
?l now sort after cukor and Opera. This is because in the Hungarian alphabet,
CS and ? are letters on their own. You can
also see that in this CI_AI collation, all four forms
of résumé have the same rank.
*/
WITH collatedwords ( collatedword,
wordno )
AS ( SELECT word COLLATE Hungarian_CI_AI ,
wordno
FROM #words
)
SELECT
collatedword ,
rank
= DENSE_RANK() OVER ( ORDER BY collatedword ) ,
wordno
FROM
collatedwords
ORDER BY
collatedword;
