Collections:
Converting Binary Strings into Unicode Character Strings in SQL Server
Can Binary Strings Be Converted into Unicode Character Strings in SQL Server Transact-SQL?
✍: FYIcenter.com
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:
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 Unicode 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.
⇒ bin2hex - Converting Binary Strings into Hexadecimal Character Strings in SQL Server
⇐ Converting Binary Strings into Character Strings in SQL Server
⇑ Character Strings and Binary Strings in SQL Server Transact-SQL
2017-02-28, 2370🔥, 0💬
Popular Posts:
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...
How To List All Login Names on the Server in SQL Server? If you want to see a list of all login name...
How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions in SQL Server Transact-...
How To Use SQL*Plus Built-in Timers in Oracle? If you don't have a stopwatch/timer and want to measu...
How To Get the Definition of a Stored Procedure Back in SQL Server Transact-SQL? If you want get the...