Hexadecimal to Binary Conversion in SQL Server 2008 R2

Question

Hexadecimal to Binary Conversion in SQL Server 2008 R2

guerillaunit

SSCarpal Tunnel

Points: 4249
More actions
October 28, 2012 at 3:45 pm

#284200

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?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234040 More actions · Answer 1

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"

guerillaunit SSCarpal Tunnel Points: 4249 More actions · Answer 2

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.

Perry Whittle SSC Guru Points: 234040 More actions · Answer 3

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

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

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

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 4

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.

Hexadecimal to Binary Conversion in SQL Server 2008 R2

Cookies on SQLServerCentral