question

BldrAppDev avatar image
BldrAppDev asked

Cast varbinary to varchar returns a blank

I use an application that stores a file path as hexadecimal converted to decimal. So, a file path of \00\00\05\B4 would be stored in the database (slashes are not stored) as 1460. I can convert this using cast(column as varbinary(8)) but I would like to convert it to text so I can provide the actual path using concatenation instead 0x000005B4. When I try to cast the above cast to varchar, it returns a blank. Is there a way to convert varbinary to text so I can parse it in the query and return a path string?
varcharcast
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DenisT avatar image
DenisT answered
You have to use CONVERT(), not CAST()! -- SQL Server 2008 and above DECLARE @int INT = 1460; SELECT CONVERT(VARCHAR(8), CONVERT(VARBINARY(8), @int, 2), 2); -- SQL Server 2005 -- please see Peter Larsson's blog post [Convert binary value to string value][1] Hope it helps! [1]: http://sqlblog.com/blogs/peter_larsson/archive/2010/01/27/convert-binary-value-to-string-value.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BldrAppDev avatar image
BldrAppDev answered
Works perfect! Thanks for your help.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.