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.
The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings.
VARBINARY is an SQL data type, nothing to do with C#.
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.
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With