SEinarsson
asked on
Convert String of binary characters to Binary
I have a string of hex characters "0x89504e". I convert this to a string of binary characters @sOutput = "100010010101000001001110"
How would I go about converting the string to binary such that the value would return as the original "0x89504e"? It's looking like the CAST is treating the string as numeric and converting a really large int to binary.
-SEinarsson-
How would I go about converting the string to binary such that the value would return as the original "0x89504e"? It's looking like the CAST is treating the string as numeric and converting a really large int to binary.
-SEinarsson-
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, that worked. What exactly is it doing here.... converting the binary string to an int, then converting the int to binary? What is the substring for? I ran it without it and saw 2 extra zeros at the end of the return value.
-SEinarsson-
-SEinarsson-
there's no built-in function to convert a string to a binary.
so cast('11110000' as binary) does not work as expected, as you noticed by yourself.
Instead, you have to use either a user-defined fuction or a built-in conversion function.
>>What exactly is it doing here.... converting the binary string to an int, then converting the int to binary?<<
exactly
>>What is the substring for?<<
since int is stored on 4 bytes, the conversion to varbinary datatype results in a 4 bytes binary. The substring function is used to get rid of the last byte.
so cast('11110000' as binary) does not work as expected, as you noticed by yourself.
Instead, you have to use either a user-defined fuction or a built-in conversion function.
>>What exactly is it doing here.... converting the binary string to an int, then converting the int to binary?<<
exactly
>>What is the substring for?<<
since int is stored on 4 bytes, the conversion to varbinary datatype results in a 4 bytes binary. The substring function is used to get rid of the last byte.
alternatively, you can use a conversion stored procedure as follows, if you don't need the "100010010101000001001110"
drop proc dbo.usp_StrToBinary
go
create proc dbo.usp_StrToBinary(@strIn
declare @sql nvarchar(500)
set @sql = N'set @b = ' + @strIn
exec sp_executesql @sql, N'@b varbinary(500) out', @binOut out
go
-- how to use it
declare @a varchar(500)
declare @b varbinary(500)
set @a = '0x89504e'
exec usp_strtobinary @a , @b out
select @b
drop proc dbo.usp_StrToBinary
go
create proc dbo.usp_StrToBinary(@strIn
declare @sql nvarchar(500)
set @sql = N'set @b = ' + @strIn
exec sp_executesql @sql, N'@b varbinary(500) out', @binOut out
go
-- how to use it
declare @a varchar(500)
declare @b varbinary(500)
set @a = '0x89504e'
exec usp_strtobinary @a , @b out
select @b
ASKER
Thanks for the help, the "0101" step isn't required, I just wasn't able to find a way to go straight from the hex to binary. That proc might come in very useful.
-SEinarsson-
-SEinarsson-
.. searching