select char(0x0041)
--A

而如果是
select char('0x0041')
该怎么办？

CREATE FUNCTION dbo.hexstr2varbin(
@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


select char(dbo.hexstr2varbin('0x0041'))