将十六进制字符串转换为Varbinary

转自http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-convert-hex-string-to-varbinary.aspx

SQL Server 2005中有一个没有文档说明的函数sys.fn_varbintohexstr，函数可把十六进制数转换为字符串表示的十六进制数（例如0x3a转换为'0x3a'），但是并没有函数把十六进制字符串转换为十六进制数的。于是便有了下面这个函数：

CREATE FUNCTION dbo.HexStrToVarBin(@hexstr VARCHAR(8000)) 

RETURNS varbinary(8000) 

AS 

 

BEGIN 

   DECLARE @hex CHAR(2), @i INT, @count INT, @b varbinary(8000), @odd BIT, @start bit

   SET @count = LEN(@hexstr) 

   SET @start = 1

   SET @b = CAST('' AS varbinary(1)) 

   IF SUBSTRING(@hexstr, 1, 2) = '0x' 

       SET @i = 3 

   ELSE 

       SET @i = 1 

   SET @odd = CAST(LEN(SUBSTRING(@hexstr, @i, LEN(@hexstr))) % 2 AS BIT)

   WHILE (@i <= @count) 

    BEGIN 

       IF @start = 1 AND @odd = 1

       BEGIN

           SET @hex = '0' + SUBSTRING(@hexstr, @i, 1)

       END

       ELSE

       BEGIN

           SET @hex = SUBSTRING(@hexstr, @i, 2)

       END

       SET @b = @b + 

               CAST(CASE WHEN SUBSTRING(@hex, 1, 1) LIKE '[0-9]' 

                   THEN CAST(SUBSTRING(@hex, 1, 1) AS INT) 

                   ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 1, 1)))-55 AS INT) 

               END * 16 + 

               CASE WHEN SUBSTRING(@hex, 2, 1) LIKE '[0-9]' 

                   THEN CAST(SUBSTRING(@hex, 2, 1) AS INT) 

                   ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 2, 1)))-55 AS INT) 

               END AS binary(1)) 

       SET @i = @i + (2 - (CAST(@start AS INT) * CAST(@odd AS INT)))

       IF @start = 1

       BEGIN

           SET @start = 0

       END

    END 

    RETURN @b 

END 

GO