Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - String Type Conversion During Concatenation

By: FYIcenter.com

(Continued from previous topic...)

What Happens When Unicode Strings Concatenate with Non-Unicode Strings?

If a Unicode string NVARCHAR is concatenated with a non-Unicode string VARCHAR, SQL Server will implicitly convert the non-Unicode string to Unicode string for concatenation.

DECLARE @regcode VARCHAR(40);
DECLARE @unicode NVARCHAR(40);
SET @regcode = 'Some Unicode characters: '
SET @unicode = NCHAR(9733)+NCHAR(9734)+NCHAR(9792)
   +NCHAR(9794);
SELECT @regcode + @unicode; 
SELECT DATALENGTH(@regcode);
SELECT DATALENGTH(@unicode);
SELECT DATALENGTH(@regcode + @unicode);
Some Unicode characters: ????
25
8
58

Note that the non-Unicode string @regcode has been converted to a Unicode string. The number of bytes of @regcode changed from 25 to 50. With 8 bytes from @unicode, the number of bytes of the concatenated string becomes 58.

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