Cast collation of nvarchar variables in t-sql

sql tsql sql-server-2008 collation string-comparison
Sandor Davidhazi picture Sandor Davidhazi · Oct 27, 2009 · Viewed 57k times · Source

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.

Answer

Lukasz Lysik picture Lukasz Lysik · Oct 27, 2009 
SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More
SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username nvarchar(100), AS BEGIN DECLARE @TopRelatedItemId uniqueidentifier; SET @TopRelatedItemId = ( SELECT top(1) RelatedItemId FROM …

Read More