Converting Binary Strings into Character Strings in SQL Server

Q

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

✍: FYIcenter.com

A

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.

 

Character Strings and Binary Strings in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2017-02-28, 366👍, 0💬