question

Pooja avatar image
Pooja asked

Convert Varbinary to Varchar

Hi,

I have a column in my table having datatype as Varbinary(20). The values in the column are populated by combining the values from other columns using SHA algorithm. Now I want to retrieve the original value in varchar datatype. How to do that. Thanks in advance

sql-server-2008cast-converthash
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

2 Answers

·
Matt Whitfield avatar image
Matt Whitfield answered

You can't reverse an SHA hash, it's a one-way algorithm...

To convert a normal varbinary to a varchar, you use CONVERT.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Grant Fritchey avatar image
Grant Fritchey answered

I agree with Matt but I'd use CAST instead of CONVERT

7 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
In general I'd agree, but I find going from varbinary to varchar without specifying style using CONVERT is usually not what is needed...
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Mainly, because MS keeps pushing CAST over CONVERT and making threats to take it away. So I try to force myself to use it everywhere I can. It's not always the best choice, but I try to build coding habits so...
1 Like 1 ·
Katie 1 avatar image Katie 1 commented ·
@ Grant,@Matt, @fatherjack, is there a way to insert a vachar values into the varbinary column?
1 Like 1 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
Remember that CAST is part of ANSI sql and convert is part of Microsoft T-SQL. Many people, especially if they use multiple RDBMS, consider it a best practice to use ANSI SQL whenever possible and only use proprietary extensions when there is a good reason to.
1 Like 1 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
@Grant - why the preference for CAST? I usually go for CAST and then if style is needed swap to CONVERT but for no reason other than habit ...
0 Likes 0 ·
SQL Kiwi avatar image SQL Kiwi commented ·
CONVERT in 2008 gives you more control over the formatting (styles 1 & 2) and is by far the fastest way to convert 0xDEADBEEF to 'DEADBEEF' or '0xDEADBEEF'. Not what is required here, but thought I'd mention it anyway. Not aware of a deprecation cycle for CONVERT?
0 Likes 0 ·
Oleg avatar image Oleg commented ·
Is there a way to insert a vachar values into the varbinary column? Yes, there is, just cast the varchar to varbinary of appropriate size. If the source is varchar then the varbinary should be of the same size, and if the source is nvarchar then the varbinary should be twice the size of the source: select cast('hello' as varbinary(5)) -- returns 0x68656C6C6Fselect cast(N'hello' as varbinary(10)) -- returns 0x680065006C006C006F00. On the way back it is the same: select cast(0x680065006C006C006F00 as nvarchar(5)) -- returns hello
0 Likes 0 ·

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.