CREATE TABLE [dbo].[tb_productSeries] (
[productSeriesID] [bigint] IDENTITY (1, 1) NOT NULL ,
[productSeries] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mac_start] [decimal](18, 0) NULL ,
[mac_end] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
表如上。现 mac_start 和 mac_end 存储的是十进制数据,要在前台显示十六进制。最大为FFFFFFFFFFFF
最小为0000000000000如何用一个sql语句,显示出来的mac_start,mac_end都是十六进制。比如第一行数据,1 av 1 281474976710655如何用select 语句。显示 av 1 FFFFFFFFFFFF
[productSeriesID] [bigint] IDENTITY (1, 1) NOT NULL ,
[productSeries] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mac_start] [decimal](18, 0) NULL ,
[mac_end] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
表如上。现 mac_start 和 mac_end 存储的是十进制数据,要在前台显示十六进制。最大为FFFFFFFFFFFF
最小为0000000000000如何用一个sql语句,显示出来的mac_start,mac_end都是十六进制。比如第一行数据,1 av 1 281474976710655如何用select 语句。显示 av 1 FFFFFFFFFFFF
RETURNS dec
AS
BEGIN
declare @iValue dec(12,2)
,@iCount int
,@iLen int
,@iPos int,
@cChar int
select @iValue=0.00,@iCount=len(replace(@vNCarryValue,'0',''))
select @vNCarryValue = ltrim(rtrim(isnull(@vNCarryValue,'')))
while(len(@vNCarryValue)>=1)
begin
set @cChar = ascii(right(@vNCarryValue,1))
if (@cChar >= ascii('0') and @cChar <= ascii('9')) or (@cChar >= ascii('a') and @cChar <= ascii('f')) or (@cChar >= ascii('A') and @cChar <= ascii('F'))
begin
select @iValue = @iValue * 16 + (case when (@cChar >= ascii('0') and @cChar <= ascii('9')) then @cChar - ascii('0')
when (@cChar >= ascii('a') and @cChar <= ascii('f')) then @cChar - ascii('a')
when (@cChar >= ascii('A') and @cChar <= ascii('F')) then @cChar - ascii('A')
end)
select @vNCarryValue=substring(@vNCarryValue,1,len(@vNCarryValue)-1)
-- ,@iLen=len(@vNCarryValue)
-- ,@iPos=@iLen-charindex('1',@vNCarryValue,0)
-- ,@vNCarryValue=right(@vNCarryValue,len(@vNCarryValue)-
-- charindex('1',@vNCarryValue))
-- ,@iValue=@iValue+power(16,@iPos)
-- ,@iCount = @iCount-1
end
else
return 0;
end
return @iValue
END
--result
/*0xFFFFFFFFFFFF*/
returns bigint
begin declare @r bigint
set @r=0 declare @i bigint
set @i=1 while @i<=len(@h)
begin
set @r=@r+ convert(int,
(
case
when substring(@h,@i,1)<='9' then substring(@h,@i,1)
when substring(@h,@i,1)<='A' then '10'
when substring(@h,@i,1)<='B' then '11'
when substring(@h,@i,1)<='C' then '12'
when substring(@h,@i,1)<='D' then '13'
when substring(@h,@i,1)<='E' then '14'
when substring(@h,@i,1)<='F' then '15'
end
))
*power(16,len(@h)-@i) set @i=@i+1 end return @r
end
go
create function inttohex(@i int)
returns varchar(15)
begin
--declare @i int
--set @i=11259375 declare @r varchar(10)
set @r=''
while @i/16>0
begin
set @r=
(case
when (@i % 16)<=9 then convert(varchar(1),@i % 16)
when (@i % 16)=10 then 'A'
when (@i % 16)=11 then 'B'
when (@i % 16)=12 then 'C'
when (@i % 16)=13 then 'D'
when (@i % 16)=14 then 'E'
when (@i % 16)=15 then 'F'
end)
+@r --select @r,@i set @i=@i/16
end --select @r,@i
if @i>0
set @r=(case
when (@i % 16)<=9 then convert(varchar(1),@i % 16)
when (@i % 16)=10 then 'A'
when (@i % 16)=11 then 'B'
when (@i % 16)=12 then 'C'
when (@i % 16)=13 then 'D'
when (@i % 16)=14 then 'E'
when (@i % 16)=15 then 'F'
end)+@r -- select @r
return @r
end
go
小F 5楼的代码不知所云。select dbo.Hex2Dec('f')结果居然是5.。。
要FFFFFFFFFFFFF 不要oxFFFFFFFFFFFF谢了~
2> go
----------------
0x00000031
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x0f000001ffffffffffff0000(所影响的行数为 1 行)
楼主是用DELPHI的吧,Ado把它取出来,用IntToHex(x,2)转下就好了
恩。这个是可以的。能改下成FF-FF-FF-FF-FF-FF的格式的吗?
master.dbo.fn_varbintohexstr是个不常用的系统函数