varchar 字符串转为 varbinary

/*****************
网上看到文章讲varbinay转varchar比较多,自己参考别人做一个相反的
varchar(64) ->varbinary
******************/
Create function fnGetBinary
(@strSource varchar(64))
returns varbinary(64)
as
begin
declare @varBinDest varbinary(64)
declare @iTextPos tinyint
set @iTextPos = 1

-- 0~9 48
-- A~F 65

while (@iTextPos < DataLength(@strSource))
begin
declare @asciiVal tinyint
declare @oneByte tinyint
set @asciiVal = ascii(substring(@strSource, @iTextPos,1))

--产生字母二进制值
if @asciiVal < 65
set @oneByte = @asciiVal - 48
else
set @oneByte = @asciiVal - 65 + 10

set @iTextPos = @iTextPos + 1

set @asciiVal = ascii(substring(@strSource, @iTextPos,1))

if @asciiVal < 65
set @oneByte = @oneByte*16 + @asciiVal - 48
else
set @oneByte = @oneByte*16 + @asciiVal - 65 + 10



if @iTextPos = 2
set @varBinDest = cast(@oneByte as binary(1))
else
set @varBinDest = @varBinDest + cast(@oneByte as binary(1))
set @iTextPos = @iTextPos + 1

end

return @varBinDest
end 

 

 

更多请访问本人站点:

http://www.caochunhua.com.cn

http://www.livecoach.cn