Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cast collation of nvarchar variables in t-sql

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.

like image 473
Sandor Davidhazi Avatar asked Oct 27 '09 16:10

Sandor Davidhazi


People also ask

What is Tsql collation?

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.

What is collate SQL_Latin1_General_CP1_CI_AS in select statement?

The collate clause is used for case sensitive and case insensitive searches in the columns of the SQL server. There are two types of collate clause present: SQL_Latin1_General_CP1_CS_AS for case sensitive. SQL_Latin1_General_CP1_CI_AS for case insensitive.

How do I collate data in SQL?

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.

What is the use of collate Latin1_General_CS_AS?

Collation Latin1_General_CS_AS uses dictionary order so both upper and lower case characters of the specified range are included. Specify a binary collation to get the behavior you want (the 26 ASCII characters in the 97-122 code point range): SELECT empid, lastname FROM HR.


1 Answers

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS 
like image 113
Lukasz Lysik Avatar answered Oct 06 '22 00:10

Lukasz Lysik