October 28, 2012 at 3:45 pm
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?
October 28, 2012 at 4:23 pm
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" 😉
October 29, 2012 at 2:51 pm
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.
October 29, 2012 at 4:05 pm
you asked why you were getting the error, i told you 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 30, 2012 at 1:39 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply