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.

-SEinarsson-
SEinarssonAsked:
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
Actually, the CAST is treating each individual 1 or 0 as litteral character (using the ASCII code)
.. searching
HilaireCommented:
please try

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

instead

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.

Start your 7-day free trial
SEinarssonAuthor 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.

-SEinarsson-
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

HilaireCommented:
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.
HilaireCommented:
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
SEinarssonAuthor 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.

-SEinarsson-
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.