Varbinary to varchar w / o master.dbo.fn_varbintohexstr

Question

Is there a way to convert varbinary to an ASCII varchar string (base64, md5, sha1 - it doesn't matter) without the master.dbo.fn_varbintohexstr function on MS SQL Server 2005? Because it cannot be used inside a computed column.

CONVERT and CAST return non-ASCII strings.

Thanks,

Denis.

Answer 1

For md5 and sha1 you can use hashbytes . To get base64, you can create udf that does the conversion and uses it in the computed column.

BinToBase64 Function:

create function BinToBase64(@Bin varbinary(max)) returns varchar(max) as begin return CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@Bin")))', 'VARCHAR(MAX)') end 

BinToHexString Function:

 create function BinToHexString(@Bin varbinary(max)) returns varchar(max) as begin return '0x' + cast('' as xml).value('xs:hexBinary(sql:variable("@Bin") )', 'varchar(max)'); end 

Use this:

 create table TestTable ( Col1 varbinary(max), Col2 as dbo.BinToHexString(hashbytes('MD5', Col1)), Col3 as dbo.BinToHexString(hashbytes('SHA1', Col1)), Col4 as dbo.BinToBase64(Col1), ) insert into TestTable values (12345) select * from TestTable 

A unique dividing restriction column using hash bytes and a uniqueidentifier column

 create table TestTable ( ID uniqueidentifier default(newid()), Col1 varbinary(max), Col2 as coalesce(hashbytes('MD5', Col1), cast(ID as varbinary(8000))) persisted ) create unique index IX_TestTable_Col2 on TestTable(Col2)