T-SQL: 关于 Varbinary(Hex,Int) 与 Varchar(HexString) 之间的数据类型转换
我以前只是用存储过程简单地实现了:
16进制数的字符串表达 转变为 真正的16进制数:
例如: '0x5f' 转换成 16进制的 0x5f
declare @ varbinary(8000)
declare @s varchar(100)
declare @sql nvarchar(100)
set @s = '0xFFFF'
set @sql = N'set @ = ' + cast(@s as nvarchar(100))
exec SP_EXECUTESQL @sql ,N'@ varbinary(8000) out', @ out
select @
该方法属于投机取巧,只实现了 HexString to Hex 的单向转换,而且不能用 "自定义函数(udf)" 实现!
利用这种方式还有一篇 blog@csdn: 实现了:
《用于执行(计算
T-SQL: 关于 Varbinary(Hex,Int) 与 Varchar(HexString) 之间的数据类型转换
我以前只是用存储过程简单地实现了:
16进制数的字符串表达 转变为 真正的16进制数:
例如: '0x5f' 转换成 16进制的 0x5f
declare @ varbinary(8000)
declare @s varchar(100)
declare @sql nvarchar(100)
set @s = '0xFFFF'
set @sql = N'set @ = ' + cast(@s as nvarchar(100))
exec SP_EXECUTESQL @sql ,N'@ varbinary(8000) out', @ out
select @
该方法属于投机取巧,只实现了 HexString to Hex 的单向转换,而且不能用 "自定义函数(udf)" 实现!
利用这种方式还有一篇 blog@csdn: 实现了:
《用于执行(计算) "字符串表达式" 的 T-SQL 存储过程》
http://blog.csdn.net/playyuer/archive/2004/12/08/208601.aspx
今天在 google 搜到
http://www.google.com/search?hl=zh-CN&newwindow=1&q=HexToSMALLINT+HexToINT&btnG=%E6%90%9C%E7%B4%A2&lr=
现成儿的,这两篇好东西:
HexToInt
http://www.sqlservercentral.com/scripts/contributions/1495.asp
-- By Aaron West, 5/4/2005
-- This version allows negative numbers
CREATE FUNCTION dbo.HexToINT
(
@Value VARCHAR(8)
)
RETURNS INT
AS
BEGIN
if @Value LIKE '%[^0-9A-Fa-f]%'
RETURN NULL
DECLARE @I BIGINT
SET @I = CAST(CAST(RIGHT(UPPER('00000000' + @Value),8) AS BINARY(8)) AS BIGINT) - CAST(0x3030303030303030 AS BIGINT)
SET @I=@I-((@I/16)&CAST(0x0101010101010101 AS BIGINT))*7
RETURN CAST(CAST(
(@I&15)
+((@I/16)&240)
+((@I/256)&3840)
+((@I/4096)&61440)
+((@I/65536)&983040)
+((@I/1048576)&15728640)
+((@I/16777216)&251658240)
+(@I/CAST(0x0100000000000000 AS BIGINT)*268435456)
AS BINARY(4))AS INT)
END
GO
SELECT
dbo.HexToINT('0ABC') ,
dbo.HexToINT('7FFF') ,
dbo.HexToINT('0FFF') ,
dbo.HexToINT('0') AS Zero,
dbo.HexToINT('7FFFFFFF') AS MaxValue,
dbo.HexToINT('80000000') AS MaxNeg,
dbo.HexToINT('FFFFFFFF') AS NegOne
faster dbo.ufn_vbintohexstr - varbinary to hex
http://www.sqlservercentral.com/scripts/contributions/1497.asp
create function dbo.ufn_vbintohexstr
(
@vbin_in varbinary(256)
)
returns varchar(514)
as
Begin
declare @x bigint
declare @len int
declare @loop int
declare @value varbinary(514)
set @value = 0x
set @loop = 1
set @len = datalength(@vbin_in)
if (@len & 1) <> 0
set @vbin_in = 0x00 + @vbin_in
if (@len & 3) < 3
set @vbin_in = 0x0000 + @vbin_in
while @loop <= @len
begin
set @x = CAST(substring(@vbin_in,@loop,4)AS BIGINT)
set @x =65536*
( (@x&0xF0000000)*4096
+(@x&0x0F000000)*256
+(@x&0x00F00000)*16
+(@x&0x000F0000) )
+(@x&0xF000)*4096
+(@x&0x0F00)*256
+(@x&0x00F0)*16
+(@x&0x000F)
set @x = (@x | 0x3030303030303030)+
((@x+0x0606060606060606)/16
& 0x0101010101010101)*7
select @value = @value + CAST(@x AS BINARY(8))
set @loop = @loop + 4
end
return '0x'+ right(CAST(@value AS VARCHAR(514)), @len*2)
end
GO
select dbo.ufn_vbintohexstr(0x123456789abcdef1234)
-- outputs: 0x0123456789ABCDEF1234
GO