Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Converting Unicode Strings to Non-Unicode Strings

By: FYIcenter.com

(Continued from previous topic...)

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

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

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

(Continued on next topic...)

1. How To Concatenate Two Character Strings Together?
2. What Happens When Unicode Strings Concatenate with Non-Unicode Strings?
3. How To Convert a Unicode Strings to Non-Unicode Strings?
4. What Are the Character String Functions Supported by SQL Server 2005?
5. How To Insert New Line Characters into Strings?
6. How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions?
7. How To Concatenate Two Binary Strings Together?
8. Can Binary Strings Be Used in Arithmetical Operations?
9. How To Convert Binary Strings into Integers?
10. Can Binary Strings Be Converted into NUMERIC or FLOAT Data Types?
11. Can Binary Strings Be Converted into Character Strings?
12. Can Binary Strings Be Converted into Unicode Character Strings?
13. How To Convert Binary Strings into Hexadecimal Character Strings
14. What Are Bitwise Operations?