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
![None.gif](/Images/OutliningIndicators/None.gif)
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