Declare @x nvarchar(1024)
Declare @y int
Declare @sys int --x进制Set @x='ff'
Set @sys=16 --@sys=21表示二十一进制--x进制转换成10进制
;With
T1 As(
Select
X=Stuff(Reverse(@x),1,1,''),
Y=Convert(int,Case Isnumeric(Right(@x,1)) When 0 Then ASCII(Upper(Right(@x,1)))-55 Else Right(@x,1) End*Power(@sys,0)),
L=Convert(int,1)
Union All
Select
X=Stuff(X,1,1,''),
Y=Convert(int,Case Isnumeric(Left(X,1)) When 0 Then ASCII(Upper(Left(X,1)))-55 Else Left(X,1) End*Power(@sys,L)),
L=L+Convert(int,1)
From T1
Where X>''
)
Select [10进制-->X进制]=SUM(Y) From T1--10进制换成x进制转
Set @y=2
Set @sys=2 --@sys=2表示二进制
;With
T1 As(
Select
X=Convert(int,@y/@sys),
Y=Convert(nvarchar(1024),Case (@y%@sys)/10 When 0 Then Rtrim(@y%@sys) Else Char(@y%@sys%10+65) End),
L=Convert(int,1)
Union All
Select
X=X/@sys,
Y=Convert(nvarchar(1024),Case (X%@sys)/10 When 0 Then Rtrim(X%@sys) Else Char(X%@sys%10+65) End+Y),
L=Convert(int,L+1)
From T1 Where X>0
)
Select Top 1 [x进制-->10进制]=Y From T1 Order By L Desc
Declare @y int
Declare @sys int --x进制Set @x='ff'
Set @sys=16 --@sys=21表示二十一进制--x进制转换成10进制
;With
T1 As(
Select
X=Stuff(Reverse(@x),1,1,''),
Y=Convert(int,Case Isnumeric(Right(@x,1)) When 0 Then ASCII(Upper(Right(@x,1)))-55 Else Right(@x,1) End*Power(@sys,0)),
L=Convert(int,1)
Union All
Select
X=Stuff(X,1,1,''),
Y=Convert(int,Case Isnumeric(Left(X,1)) When 0 Then ASCII(Upper(Left(X,1)))-55 Else Left(X,1) End*Power(@sys,L)),
L=L+Convert(int,1)
From T1
Where X>''
)
Select [10进制-->X进制]=SUM(Y) From T1--10进制换成x进制转
Set @y=2
Set @sys=2 --@sys=2表示二进制
;With
T1 As(
Select
X=Convert(int,@y/@sys),
Y=Convert(nvarchar(1024),Case (@y%@sys)/10 When 0 Then Rtrim(@y%@sys) Else Char(@y%@sys%10+65) End),
L=Convert(int,1)
Union All
Select
X=X/@sys,
Y=Convert(nvarchar(1024),Case (X%@sys)/10 When 0 Then Rtrim(X%@sys) Else Char(X%@sys%10+65) End+Y),
L=Convert(int,L+1)
From T1 Where X>0
)
Select Top 1 [x进制-->10进制]=Y From T1 Order By L Desc
@binary varbinary(255),
@str_return varchar(255) SELECT
@binary = CONVERT(varbinary(255),29327795562177529)
EXEC master.dbo.xp_varbintohexstr @binary, @str_return OUTPUT
SELECT
结果 = reverse(@str_return)/*
结果
__________________________
008613974878369F10000011x0
*/
DECLARE
@binary varbinary(255),
@str_return varchar(255) SELECT
@binary = CONVERT(varbinary(255),cast(29327795562177529 as bigint))
EXEC master.dbo.xp_varbintohexstr @binary, @str_return OUTPUT
SELECT
结果 = reverse(@str_return)/*
结果
-------------------
9F36784897138600x0
*/
不过还是需要处理0x00...这样的内容以实现格式化。
将二进制表达式的内容转成字符串格式,如:0xFFFF ==> '0xFFFF'
DECLARE @bi BIGINT, @hexstr VARCHAR(32)
SET @bi=29327795562177529SELECT @hexstr=UPPER(STUFF(sys.fn_varbintohexstr(CAST(@bi AS VARBINARY(64))),1,2,''))WHILE SUBSTRING(@hexstr,1,2)='00'
SET @hexstr=STUFF(@hexstr,1,2,'') SELECT @bi, @hexstr
/*
-------------------- --------------------------------
29327795562177529 683179848763F9(1 行受影响)
*/
RETURNS varchar(16)
AS
BEGIN--@num为要转换为十六进制进制字符串的正整数,若为负数则返回'0'
--@length为要返回的十六进制字符串的长度
--若@length为负数则返回'0',若为0则返回实际长度的十六进制字符串
--若@length大于0则返回长度为@length的十六进制字符串(从低位起,位数不够高位补'0',超过位数则高位被截断)
DECLARE @result varchar(16)
SET @result=''
IF @num<=0 or @length<0
SET @result='0'
ELSE
BEGIN
WHILE @num<>0
SELECT @result=SUBSTRING('0123456789ABCDEF',@num%16+1,1)+@result,@num=@num/16
IF @length>0
SET @result=RIGHT(REPLICATE('0',@length)+@result,@length)
END
RETURN (@result)
END
DECLARE
@binary varbinary(255),
@str_return varchar(255) SELECT
@binary = CONVERT(varbinary(255),cast(29327795562177529 as bigint))
EXEC master.dbo.xp_varbintohexstr @binary, @str_return OUTPUT SET @str_return=stuff(@str_return,1,2,'')WHILE left(@str_return,2)='00'
SET @str_return=stuff(@str_return,1,2,'')SELECT 结果= REVERSE(@str_return)/*
结果
-------------------
9F367848971386
*/
DECLARE @bi BIGINT, @hexstr VARCHAR(32)
SET @bi=29327795562177529SELECT @hexstr=UPPER(STUFF(master.sys.fn_varbintohexstr(CAST(@bi AS VARBINARY(64))),1,2,''))WHILE SUBSTRING(@hexstr,1,2)='00'
SET @hexstr=STUFF(@hexstr,1,2,'')DECLARE @hexstr2 VARCHAR(32)
SELECT @hexstr2=REVERSE(@hexstr), @hexstr=''WHILE LEN(@hexstr2)>0
SELECT @hexstr=LEFT(@hexstr2,2)+@hexstr
,@hexstr2=STUFF(@hexstr2,1,2,'') SELECT @bi, @hexstr
/*
-------------------- --------------------------------
29327795562177529 8613974878369F(1 行受影响)
*/
用的SQL2005吗?master.sys.fn_varbintohexstr
DECLARE @bi BIGINT, @hexstr VARCHAR(32)
SET @bi=29327795562177529SELECT @hexstr=UPPER(STUFF(master.sys.fn_varbintohexstr(CAST(@bi AS VARBINARY(64))),1,2,''))WHILE SUBSTRING(@hexstr,1,2)='00'
SET @hexstr=STUFF(@hexstr,1,2,'')DECLARE @hexstr2 VARCHAR(32)
SELECT @hexstr2=REVERSE(@hexstr), @hexstr=NULLWHILE LEN(@hexstr2)>0
SELECT @hexstr=LEFT(@hexstr2,2)+isnull(' '+@hexstr, '')
,@hexstr2=STUFF(@hexstr2,1,2,'') SELECT @bi, @hexstr
/*
-------------------- --------------------------------
29327795562177529 86 13 97 48 78 36 9F(1 行受影响)
*/
Declare @sys int --x进制Set @y=29327795562177529
Set @sys=16 ;With
T1 As(
Select
X=Convert(bigint,@y/@sys),
Y=Convert(nvarchar(1024),Case (@y%@sys)/10 When 0 Then Rtrim(@y%@sys) Else Char(@y%@sys%10+65) End),
L=1
Union All
Select
X=X/@sys,
Y=Convert(nvarchar(1024)
,Case Len(Y)%2
when 0 then Case (X%@sys)/10 When 0 Then Rtrim(X%@sys) Else Char(X%@sys%10+65) End+Y
Else isnull(stuff(Y,2,0,Case (X%@sys)/10 When 0 Then Rtrim(X%@sys) Else Char(X%@sys%10+65) End),Y+Case (X%@sys)/10 When 0 Then Rtrim(X%@sys) Else Char(X%@sys%10+65) End)
End),
L=L+1
From T1 Where X>0
)
Select Top 1 [自定义16进制]=Y From T1 Order By L Desc
/*
自定义16进制
-------------------
8613974878369F
*/
我的机器装的是SQL Server 2000开发人员版。
returns varchar(100)
as
begin
declare @re varchar(100),@s varchar(100)
set @re=''
set @s = ''
declare @i int
set @i = 0
while @num>0
begin
select @s=substring('0123456789ABCDEF',@num%16+1,1)+@s
,@num=@num/16,@i = @i + 1
if( @I%2 = 0 )
begin
select @re=reverse(@s)+' '+ @re
set @s = ''
end
end
return(ltrim(@re))
end
go
select dbo.f_int2hex(29327795562177529)drop function f_int2hex
/*
----------------------------------------------------------------------------------------------------
86 13 97 48 78 36 9F (所影响的行数为 1 行)
*/
returns varchar(100)
as
begin
declare @re varchar(100),@s varchar(100)
set @re=''
set @s = ''
declare @i int
set @i = 0
while @num>0
begin
select @s=substring('0123456789ABCDEF',@num%16+1,1)+@s
,@num=@num/16,@i = @i + 1
if( @I%2 = 0 )
begin
select @re=reverse(right('00'+@s,2))+' '+ @re
set @s = ''
end end
if len(@s) > 0
select @re=reverse(right('00'+@s,2))+' '+ @re
return(ltrim(@re))
end
go
@binary varbinary(255),
@str_return varchar(255) SELECT
@binary = CONVERT(varbinary(255),29327795562177529)
EXEC master.dbo.xp_varbintohexstr @binary, @str_return OUTPUT
SELECT
结果 = reverse(@str_return)/*
结果
__________________________
008613974878369F10000011x0
*/--十六进制转为十进制
CREATE FUNCTION dbo.f_hex_dec(@s varchar(16))
RETURNS bigint
AS
BEGIN
--作者:pbsql
--参数不得含'0'~'9'、'a'~'f'、'A'~'F'之外的任意字符(首尾空格除外),否则返回0
DECLARE @i int,@result bigint
SELECT @i=0,@result=0,@s=RTRIM(LTRIM(UPPER(REVERSE(@s))))
WHILE @i<LEN(@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(16,@i),@i=@i+1
END
RETURN @result
END
GO
returns char(1)
as
begin
declare @c char(1)
if @n<10
set @c=convert(char(1),@n)
else
set @c=char(@n+55)
return @c
end
go
create function longhex(@n bigint)
returns varchar(20)
as
begin
declare @return varchar(20),@t char(1)
set @return=''
while @n>0
begin
set @t=dbo.hex(@n % 16)
set @n=@n/16
set @return=@t+dbo.hex(@n%16)+@return
set @n=@n/16
end
return @return
end
go
select dbo.longhex(29327795562177529)
go
drop function dbo.hex,dbo.longhex
/*
--------------------
8613974878369F
*/