Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Converting Binary Strings into Character Strings

By: FYIcenter.com

(Continued from previous topic...)

Can Binary Strings Be Converted into Character Strings?

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.

(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?