22,209
社区成员
发帖
与我相关
我的任务
分享
create function hex(@n int)
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
*/
--十进制转为十六进制
DECLARE
@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
create function f_int2hex(@num bigint)
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
create function f_int2hex(@num bigint)
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 行)
*/
Declare @y bigint
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
*/
--如果需要在中间加空格的话:
--SQL2005
DECLARE @bi BIGINT, @hexstr VARCHAR(32)
SET @bi=29327795562177529
SELECT @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=NULL
WHILE 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 行受影响)
*/