Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

varbinary to varchar w/o master.dbo.fn_varbintohexstr

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

CONVERT and CAST return non-ASCII strings.

Thank you,

Denis.

like image 754
Denis Avatar asked Jul 09 '11 19:07

Denis


People also ask

What is the difference between varchar and VARBINARY?

The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings.

What is VARBINARY C#?

VARBINARY is an SQL data type, nothing to do with C#.

What is VARBINARY Max in SQL Server?

varbinary [ ( n | max ) ] n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length.

Is VARBINARY a string?

The BINARY and BINARY VARYING (VARBINARY) data types are collectively referred to as binary string types and the values of binary string types are referred to as binary strings. A binary string is a sequence of octets or bytes. BYTEA and RAW are synonyms for VARBINARY.


1 Answers

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

Function BinToBase64:

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

Function BinToHexString:

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 like 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

Unique constraint varbinary column using hashbytes and an 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)
like image 121
Mikael Eriksson Avatar answered Sep 28 '22 07:09

Mikael Eriksson