# 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". I then run SELECT CAST(@sOutput AS BINARY). This returns me "0x313030303130303130313031303030303031303031313130000000000000".

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.

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Billing EngineerCommented:
Actually, the CAST is treating each individual 1 or 0 as litteral character (using the ASCII code)
.. searching
Commented:

select substring(cast(fn_replbitstringtoint(@sOutput) as binary(4)),1,3)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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.

Commented:
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.
Commented:
alternatively, you can use a conversion stored procedure as follows, if you don't need the "100010010101000001001110" step.

drop proc dbo.usp_StrToBinary
go
create proc dbo.usp_StrToBinary(@strIn varchar(500), @binOut varbinary(500) out) as
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
Author Commented:
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.