Hexadecimal to Binary Conversion in SQL Server 2008 R2

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Hexadecimal to Binary Conversion in SQL...

Hexadecimal to Binary Conversion in SQL Server 2008 R2

Rate Topic

Display Mode

Topic Options

Author

Message

guerillaunit

Posted Sunday, October 28, 2012 3:45 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 8:48 AM
Points: 149, Visits: 329

Hello, I am using the following code to try to convert my hexadecimal string to binary format:

DECLARE @MYHEX AS VARCHAR(MAX)

SET @MYHEX = '0000C180880810010000'

SELECT CONVERT(VARBINARY(MAX), @MYHEX, 1)

I get this error when I run it:

Error converting data type varchar to varbinary.

What am I doing wrong?

Post #1378060

Perry Whittle

Posted Sunday, October 28, 2012 4:23 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 5,242, Visits: 11,262

guerillaunit (10/28/2012)

Hello, I am using the following code to try to convert my hexadecimal string to binary format:

DECLARE @MYHEX AS VARCHAR(MAX)

SET @MYHEX = '0000C180880810010000'

SELECT CONVERT(VARBINARY(MAX), @MYHEX, 1)

I get this error when I run it:

Error converting data type varchar to varbinary.

What am I doing wrong?

Books online states that if using style 1, which you have specified, that the string must include 0x like so

DECLARE @MYHEX AS VARCHAR(MAX)

SET @MYHEX = '0x0000C180880810010000'

SELECT CONVERT(VARBINARY(MAX), @MYHEX, 1)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1378062

guerillaunit

Posted Monday, October 29, 2012 2:51 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 8:48 AM
Points: 149, Visits: 329

Perry's solution didn't work either. It fixed the error, but the function just returned the input. I ended up concocting a custom function to do the job.

CREATE FUNCTION [dbo].[HexToBinary] 
(
@hex varchar(200) 
)

RETURNS varchar(1000)

AS

BEGIN

SET @HEX=REPLACE (@HEX,'0','0000')
set @hex=replace (@hex,'1','0001')
set @hex=replace (@hex,'2','0010')
set @hex=replace (@hex,'3','0011')
set @hex=replace (@hex,'4','0100')
set @hex=replace (@hex,'5','0101')
set @hex=replace (@hex,'6','0110')
set @hex=replace (@hex,'7','0111')
set @hex=replace (@hex,'8','1000')
set @hex=replace (@hex,'9','1001')
set @hex=replace (@hex,'A','1010')
set @hex=replace (@hex,'B','1011')
set @hex=replace (@hex,'C','1100')
set @hex=replace (@hex,'D','1101')
set @hex=replace (@hex,'E','1110')
set @hex=replace (@hex,'F','1111')

RETURN @hex
END

Hope this is helpful to anyone out there in the future.

Post #1378478

Perry Whittle

Posted Monday, October 29, 2012 4:05 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 5,242, Visits: 11,262

you asked why you were getting the error, i told you Wink

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1378499

patrickmcginnis59

Posted Tuesday, October 30, 2012 1:39 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333

guerillaunit (10/29/2012)

Perry's solution didn't work either. It fixed the error, but the function just returned the input. I ended up concocting a custom function to do the job.

CREATE FUNCTION [dbo].[HexToBinary] 
(
@hex varchar(200) 
)

RETURNS varchar(1000)

AS

BEGIN

SET @HEX=REPLACE (@HEX,'0','0000')
set @hex=replace (@hex,'1','0001')
set @hex=replace (@hex,'2','0010')
set @hex=replace (@hex,'3','0011')
set @hex=replace (@hex,'4','0100')
set @hex=replace (@hex,'5','0101')
set @hex=replace (@hex,'6','0110')
set @hex=replace (@hex,'7','0111')
set @hex=replace (@hex,'8','1000')
set @hex=replace (@hex,'9','1001')
set @hex=replace (@hex,'A','1010')
set @hex=replace (@hex,'B','1011')
set @hex=replace (@hex,'C','1100')
set @hex=replace (@hex,'D','1101')
set @hex=replace (@hex,'E','1110')
set @hex=replace (@hex,'F','1111')

RETURN @hex
END

Hope this is helpful to anyone out there in the future.

I didn't like it. It returned varchar bunch of ones and zeros which is about as binary as this post I'm typing. I liked Perry's solution better as it actually did the conversion.

Post #1379001

« Prev Topic | Next Topic »

Permissions