字串（varchar）和二進位（varbinary）互轉

參考資料:

1、http://blog.sina.com.cn/s/blog_4d2d94f20100ldx6.html  
2、http://blog.csdn.net/chelen_jak/article/details/79385348

/*功能：字串(16進位)轉barbinary(可變長度二進位)說明：Sql2008 R2上測試通過*/CREATE FUNCTION dbo.hexstr2varbin(@hexstr varchar(max))RETURNS varbinary(max)ASBEGIN    DECLARE @value int    DECLARE @ascii int    DECLARE @varbin varbinary(max)    IF @hexstr LIKE '0x%'        SET @hexstr = STUFF(@hexstr,1,2,'')    SET @hexstr = UPPER(@hexstr)    IF @hexstr NOT LIKE '%[^0-9A-F]%' COLLATE Chinese_PRC_BIN    BEGIN        SET @varbin = 0x        WHILE @hexstr <> ''        BEGIN            SET @value = ASCII(SUBSTRING(@hexstr,1,1))            IF @value <= 57                SET @value = @value - 48            ELSE                SET @value = @value - 55            SET @ascii = @value * 16            SET @value = ASCII(SUBSTRING(@hexstr,2,1))            IF @value <= 57                SET @value = @value - 48            ELSE                SET @value = @value - 55            SET @ascii = @ascii + @value            SET @varbin = @varbin + CAST(@ascii AS binary(1))            SET @hexstr = STUFF(@hexstr,1,2,'')        END    END    RETURN @varbinEND/*測試代碼*/declare @str nvarchar(max)declare @bary varbinary(max)-- 原字元set @str = N'abc中國12中國3香港中華１２８467號@<>「」xyza'    -- 注意：前加加上N-- 轉16進位set @bary= cast(@str as varbinary(max))select @bary-- bin -> stringdeclare @s1 nvarchar(max)--set @s1 = sys.fn_varbintohexstr(@bary)-- 有0x開頭set @s1 = sys.fn_varbintohexsubstring(0, @bary, 1, 0)-- 無0x開頭 (第1個參數表示是否保留0x首碼，1為保留，0為不保留) select @s1 -- string -> bin declare @bin varbinary(max)set @bin = dbo.hexstr2varbin(@s1)                        -- 自訂函數select @bin-- 還原select convert(nvarchar(max), @bin)