Cast collation of nvarchar variables in t-sql

Question

I need to change the collation of an nvarchar variable. By documentation:

(...) 3. The COLLATE clause can be specified at several levels. These include the following:

Casting the collation of an expression. You can use the COLLATE clause to apply a character expression to a certain collation. Character literals and variables are assigned the default collation of the current database. Column references are assigned the definition collation of the column. For the collation of an expression, see Collation Precedence (Transact-SQL).

However I can't figure out the correct syntax for the usage of CAST(), CONVERT() or variable declaration with DECLARE for this purpose.

score 44 · Accepted Answer · answered Oct 27 '09 at 16:14

44

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

answered Oct 27 '09 at 16:14

Lukasz Lysik

10,462
3
51
72

This comes from here: http://msdn.microsoft.com/en-us/library/aa226054%28SQL.80%29.aspx – penguat Jul 28 '10 at 13:34

nalply · Answer 2 · 2012-11-10T11:57:08.883

22

CAST or CONVERT is superfluous!

SELECT N'abc' COLLATE French_CS_AS

It is superfluous because just changing the collation does not change the data type NVARCHAR.

edited Nov 10 '12 at 11:57

answered Feb 24 '12 at 10:05

nalply

26,770
15
78
101

Got here trying to cast an NVARCHAR to a VARCHAR of a specified collation. (Note the actual characters that can be stored in a VARCHAR vary with the collation.) – Joshua Jul 08 '20 at 22:40
@Joshua perhaps this is the wrong question because it's not about VARCHAR. – nalply Jul 09 '20 at 15:16

score 5 · Answer 3 · edited May 29 '12 at 15:00

If you are changing between 2 and 1 byte, or vice-ver-sa, character encodings then CAST or Convert is necessary. It is not superfluous in these cases.

When the source column is a 2 byte character sequence (nchar, nvarchar) and the selection projection is required to be a single byte character (char, varchar), you should specify the cast and convert. Apply the collation conversion before the casting between the type systems.

SELECT CAST(N'ФBC' COLLATE SQL_Latin1_General_CP1_CI_AS as varchar(10)) AS ProjectedSingleByte

score 1 · Answer 4 · answered Sep 04 '18 at 09:03

If you would like to compare or join two columns of different collation this could help. In my case I had to compare two columns with one using 'SQL_Latin1_General_CP1_CI_AS' and the other using 'Latin1_General_CP1_CI_AS'.

I simply used this option where i join these two.

on A.Person = B.NAME collate database_default

Cast collation of nvarchar variables in t-sql

4 Answers4

Linked