Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Hexadecimal to Binary Conversion in SQL Server 2008 R2 Expand / Collapse
Author
Message
Posted Sunday, October 28, 2012 3:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 8, 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
Posted Sunday, October 28, 2012 4:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 6,630, Visits: 14,206
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"
Post #1378062
Posted Monday, October 29, 2012 2:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 8, 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
Posted Monday, October 29, 2012 4:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 6,630, Visits: 14,206
you asked why you were getting the error, i told you

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1378499
Posted Tuesday, October 30, 2012 1:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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 »

Add to briefcase

Permissions Expand / Collapse