varbinary vers varchar sans master.dbo.fn_varbintohexstr

Question

Existe-t-il un moyen de convertir un fichier varbinaire en chaîne de caractères ASCII varchar (base64, md5, sha1 - peu importe) sans la fonction master.dbo.fn_varbintohexstr sur MS SQL Server 2005 ? Parce qu'elle ne peut pas être utilisée à l'intérieur d'une colonne calculée.

CONVERT et CAST renvoient des chaînes de caractères non ASCII.

Merci,

Denis.

Answer 1

Pour md5 et sha1, vous pouvez utiliser hashbytes . Pour obtenir la base64, vous pouvez créer un udf qui fait la conversion et l'utiliser dans votre colonne calculée.

Fonction 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

Fonction 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

Utilisez comme ça :

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

Colonne varbinaire à contrainte unique utilisant des hashbytes et une colonne à identifiant unique

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)