--返回MD5字符串
select dbo.varbin2Hexstr(HASHBYTES('MD5', 'test123'))
--返回MD5base64
select dbo.FnBase64Encode(HASHBYTES('MD5', 'test123'))
--Base64编码
CREATE FUNCTION [dbo].[FnBase64Encode](@str varchar(max))
RETURNS VARCHAR(max)
as
begin
declare @bin varbinary(max)
set @bin=Convert(varbinary(max), @str)
return cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(max)')
end
GO
--字符串
CREATE FUNCTION [dbo].[varbin2Hexstr](
@bin VARBINARY(8000)
)RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @re VARCHAR(8000),@i INT
SELECT @re='',@i=DATALENGTH(@bin)
WHILE @i>0
SELECT @re=SUBSTRING('0123456789ABCDEF',SUBSTRING(@bin,@i,1)/16+1,1)
+SUBSTRING('0123456789ABCDEF',SUBSTRING(@bin,@i,1)%16+1,1)
+@re
,@i=@i-1
-- return('0x'+@re)
RETURN @re
END
GO