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'))
execute('select char(0x0041)')
@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'))