How To Convert a Unicode Strings to Non-Unicode Strings in SQL Server Transact-SQL?

Since Unicode character set is different than code page based (non-Unicode) character set, converting Unicode strings to non-Unicode strings may result in wrong characters or missing characters. So you should avoid converting Unicode strings to non-Unicode strings.

If you really want to, there are 3 ways to convert a Unicode string to a non-Unicode string:

• Implicit conversion by assignment operations - When a Unicode string is assigned to a variable, a column, or a parameter of a non-Unicode string data type, SQL Server will implicitly convert the Unicode string to a non-Unicode string.
• Explicit conversion using the CAST() function - A Unicode string can be explicitly converted to non-Unicode string using the CAST(Unicode_string AS VARCHAR(size)) function.
• Explicit conversion using the CONVERT() function - A Unicode string can be explicitly converted to non-Unicode string using the CONVERT(VARCHAR(size), Unicode_string) function.

Some numeric data type conversion examples are provided in the tutorial exercise below:

-- Implicit conversion by an assignment operation
DECLARE @regcode VARCHAR(40);
SET @regcode = N'Some Unicode characters: '
   + NCHAR(9733)+NCHAR(9734)+NCHAR(9792)+NCHAR(9794);
SELECT @regcode;
GO
Some Unicode characters: ????

-- Explicit conversion by CAST()
SELECT CAST(N'Some Unicode characters: '
   + NCHAR(9733)+NCHAR(9734)+NCHAR(9792)+NCHAR(9794)
   AS VARCHAR(40));
GO
Some Unicode characters: ????

-- Explicit conversion by CONVERT()
SELECT CONVERT(VARCHAR(40), N'Some Unicode characters: '
   + NCHAR(9733)+NCHAR(9734)+NCHAR(9792)+NCHAR(9794));
GO
Some Unicode characters: ????

⇒ Character String Functions Supported by SQL Server 2005 in SQL Server

⇐ String Type Conversion During Concatenation in SQL Server

⇑ Character Strings and Binary Strings in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials