37

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Sandor Davidhazi
  • 888
  • 1
  • 9
  • 20

4 Answers4

44
SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS
Lukasz Lysik
  • 10,462
  • 3
  • 51
  • 72
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.

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
5

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
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
1

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