Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Converting Binary Strings into Unicode Character Strings

By: FYIcenter.com

(Continued from previous topic...)

Can Binary Strings Be Converted into Unicode Character Strings?

Can binary strings be converted into Unicode character strings? The answer is yes. But you need to know how Unicode characters are represented in a binary format. Remember the following simple rules:

  • An ASCII character is stored in 1 byte.
  • A Unicode character is stored in 2 bytes.
  • When an ASCII character is mapped to a Unicode character, the first type equals to the same code value as the ASCII character, and the second type equals to 0x00.

The tutorial exercise below shows you some interesting conversion behaviors:

-- 1. Binary representation of ASCII characters 
SELECT CONVERT(VARBINARY(40),'FYIcenter.com');
GO
0x46594963656E7465722E636F6D

-- 2. Binary representation of Unicdode characters 
SELECT CONVERT(VARBINARY(40),N'FYIcenter.com');
GO
0x460059004900630065006E007400650072002E0063006F006D00

-- 3. Converting a binary string back to Unicode characters
SELECT CONVERT(NVARCHAR(40),
   0x460059004900630065006E007400650072002E0063006F006D00);
GO
FYIcenter.com

-- 4. Converting a binary string back to ASCII characters
SELECT CONVERT(VARCHAR(40),
   0x460059004900630065006E007400650072002E0063006F006D00);
GO
F

-- 5. Converting a binary string back to Unicode characters
SELECT CONVERT(NVARCHAR(40),0x46594963656E7465722E636F6D);
GO
??????m

-- 6. Converting a binary string back to ASCII characters
SELECT CONVERT(VARCHAR(40),0x46594963656E7465722E636F6D);
GO
FYIcenter.com

Test 4 seems to be giving a wrong result. It should be something like "F Y I c e n t e r . c o m ". But the client tool does not know how to display the 0x00 ASCII character and stops showing the rest of the characters.

Result of test 5 shows real characters on the SQL Server Management Studio windows. But they can not be represented on this Web page, because it is not set to handle Unicode characters. This is why you see (?) 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?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...