How To Convert a Unicode Strings to Non-Unicode Strings?

Submitted by: Administrator
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), Unicdoe_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: ????

Submitted by: Administrator

-- 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: ????

Submitted by: Administrator

Read Online MS SQL Server Job Interview Questions And Answers