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.

 

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

2017-02-28, 2141🔥, 0💬