Cast Collation of Nvarchar Variables in T-Sql

Cast collation of nvarchar variables in t-sql

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

creating a Database's Collation from variable

Here is a SQL script what you can use

USE master;  
GO  
IF DB_ID (N'DATA') IS NOT NULL  
DROP DATABASE DATA;  
GO  
DECLARE @COLLATE Nvarchar(50) 
SET @COLLATE ='Latin1_General_100_CS_AS_SC'

DECLARE @SQL nvarchar(max)
set @SQL = 'CREATE DATABASE DATA COLLATE ' + @COLLATE; 
exec sp_executeSQL @SQL

Please be careful, since it runs a DROP database command

For more on building dynamic SQL you can review mentioned article

SQL column collation change

Unfortunately SQL Server does not support OEM code page 852 which is what you need to convert code page 850 data into if you want to convert 'ØùÒ' to 'łŚń'. You can change the collation of data without SQL Server doing character mapping by CASTing through varbinary, but this only works with supported collations.

An alternative approach might be to create a user-defined function that takes a string and maps characters one-at-a-time, so Ø maps to ł etc. Fiddly to do, there are (up to) 127 characters to map, but not difficult.

How to store different collation text in SQL Server sql_variant type?

To answer your question, yes, you can store different collations in a sql_variant, however, your COLLATE statement is in the wrong place. You are changing the collation of the value after the nvarchar has been converted to a varchar, so the characters have already been lost. Converting a varchar back to an nvarchar, or changing it's collation afterwards doesn't restore "lost" data; it has already been lost.

Even if you fix that, you'll notice, however, you don't get the results you want:

USE Sandbox;
GO

CREATE TABLE TestT (TheVarchar sql_variant)
INSERT INTO dbo.TestT (TheVarchar)
SELECT CONVERT(varchar, N'향찰/鄕札 구결/口訣 이두/吏讀' COLLATE Korean_100_CI_AS)
INSERT INTO dbo.TestT (TheVarchar)
SELECT CONVERT(varchar, N'　♪リンゴ可愛いや可愛いやリンゴ。半世紀も前に流行した「リンゴの' COLLATE Japanese_CI_AS);

SELECT *
FROM dbo.TestT;
GO

DROP TABLE dbo.TestT;

Notice that the second string has the value '　♪リンゴ可愛いや可愛いやリン' (it's been truncated). That's because you haven't declared your length value for varchar. Always declare your lengths, precisions, scales, etc. You know your data better than I, so you will know an appropriate value for it.

---

Related Topics