Can Binary Strings Be Converted into Character Strings in SQL Server Transact-SQL?

Binary strings and character strings are convertible. But there are several rules you need to remember:

• Binary strings can be converted implicitly to character strings by assignment operations.
• Binary strings can not be converted implicitly to character strings by concatenation operations.
• Binary strings can be converted explicitly to character strings by CAST() and CONVERT() functions.
• Character strings can not be converted implicitly to binary strings by assignment operations.
• Character strings can not be converted implicitly to binary strings by concatenation operations.
• Character strings can be converted explicitly to binary strings by CAST() and CONVERT() functions.

For examples, see the tutorial exercise below:

-- Implicit conversion to character strings
DECLARE @char_string VARCHAR(40);
SET @char_string = 0x46594963656E7465722E636F6D;
SELECT @char_string;
GO
FYIcenter.com

-- Implicit conversion to binary strings
DECLARE @binary_string VARBINARY(40);
SET @binary_string = 'FYIcenter.com';
GO
Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type varchar to varbinary is
   not allowed. Use the CONVERT function to run this query.

-- Explicit conversion to binary strings
DECLARE @binary_string VARBINARY(40);
SET @binary_string = CONVERT(VARBINARY(40),'FYIcenter.com');
SELECT @binary_string;
GO
0x46594963656E7465722E636F6D

-- Implicit conversion in concatenation operation 
   with character strings
SELECT 'Welcome to ' 
   + 0x46594963656E7465722E636F6D;
GO
Msg 402, Level 16, State 1, Line 1
The data types varchar and varbinary are incompatible 
   in the add operator.

⇒ Converting Binary Strings into Unicode Character Strings in SQL Server

⇐ Converting Binary Strings into NUMERIC or FLOAT in SQL Server

⇑ Character Strings and Binary Strings in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials