字符串各进制之间的转换

--16进制转10进制

CREATE FUNCTION dbo.F_Hex_Dec(@s varchar(max))  
RETURNS bigint  
AS  
BEGIN  
    DECLARE @i BIGINT ,  
        @result BIGINT 
         
    DECLARE @data BIGINT  
	SET @data =16
    
    SELECT  @i = 0 ,  
            @result = 0 ,  
            @s = RTRIM(LTRIM(UPPER(REVERSE(@s))))  
    WHILE @i < DATALENGTH(@s)   
        BEGIN  
            IF SUBSTRING(@s, @i + 1, 1) NOT BETWEEN '0' AND '9'  
                AND SUBSTRING(@s, @i + 1, 1) NOT BETWEEN 'A' AND 'F'   
                BEGIN  
                    SELECT  @result = 0  
                    BREAK  
                END  
            SELECT  @result = @result + ( CHARINDEX(SUBSTRING(@s, @i + 1, 1),  
                                                    '0123456789ABCDEF') - 1 )  
                    * POWER(@data, @i) ,  
                    @i = @i + 1  
        END  
    RETURN @result  
END 

--16进制转2进制

CREATE FUNCTION dbo.F_Hex_Binary(
@hexstr varchar(max)
)
RETURNS varbinary(max)
AS
/*
将表示16进制的字符串转换为2进制类型
--TESTCASES
SELECT dbo.hexstr2varbin(NULL),NULL
SELECT dbo.hexstr2varbin(''),0x
SELECT dbo.hexstr2varbin('0x'),0x
SELECT dbo.hexstr2varbin('30394161'),0x30394161
SELECT dbo.hexstr2varbin('0x30394161'),0x30394161
SELECT dbo.hexstr2varbin('0x1A2B3C4D5E6F'),0x1A2B3C4D5E6F
SELECT dbo.hexstr2varbin('0x1a2b3c4d5e6f'),0x1a2b3c4d5e6f
--UNIMPLEMENTED
SELECT dbo.hexstr2varbin('0x3039416'),0x3039416
*/
BEGIN
    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 @varbin
END
GO

另外可以使用系统函数

--string到binary可以用这个系统函数sys.fn_varbintohexstr()（实际上是master.dbo.fn_varbintohexstr）
SELECT sys.fn_varbintohexstr(0x1234),'0x1234'