求十进制转换十六进制sql语句。

[phoeni_xin]

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

[--小F--]

CREATE   FUNCTION   dbo.Hex2Dec     (@vNCarryValue   varchar(100))   
  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

[chuifengde]

SELECT cast(cast(281474976710655 AS BIGINT) as varbinary(6))
--result
/*0xFFFFFFFFFFFF*/

[--小F--]

create    function hextoint(@h varchar(8)) 
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

[phoeni_xin]

[Quote=引用 5 楼 fredrickhu 的回复:]
SQL codeCREATEFUNCTION dbo.Hex2Dec (@vNCarryValuevarchar(100))RETURNSdecASBEGINdeclare@iValuedec(12,2)
,@iCountint
,@iLenint
,@iPosint,@cCharintselect@iValue=0.00,@iCount=len(replace(@vNCarryValue,'0',''))select@vNCarryValue=ltrim(rtrim(isnull(@vNCarryValue,'')))while(len(@vNCarryValue)>=1)beginset@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'))beginselect@iValue=@iValue*16+ (casewhen (@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-1endelsereturn0;endreturn@iValueEND
[/Quote]

小F 5楼的代码不知所云。

select dbo.Hex2Dec('f')

结果居然是5.。。

[phoeni_xin]

hextoint

inttohex

居然还是int类型的。。

[phoeni_xin]

各位哥。来帮忙下啊。

[chuifengde]

六楼不行啊???????

[phoeni_xin]

[Quote=引用 11 楼 chuifengde 的回复:]
六楼不行啊???????
[/Quote]

要FFFFFFFFFFFFF

不要oxFFFFFFFFFFFF

谢了~

[chuifengde]

SELECT stuff(master.dbo.fn_varbintohexstr(cast(cast(281474976710655 AS BIGINT) as varbinary(6))),1,2,'')

[ACMAIN_CHM]

master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(16),@x))

[ACMAIN_CHM]

1> SELECT master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(16),49))
2> go
----------------
0x00000031

[phoeni_xin]

[Quote=引用 15 楼 acmain_chm 的回复:]
1> SELECT master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(16),49))
2> go
----------------
0x00000031
[/Quote]
SELECT master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(16),281474976710655))


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x0f000001ffffffffffff0000

（所影响的行数为 1 行）

[fa_ge]

[Quote=引用 9 楼 phoeni_xin 的回复:]
hextoint

inttohex

居然还是int类型的。。
[/Quote]

楼主是用DELPHI的吧，Ado把它取出来，用IntToHex(x,2)转下就好了

[phoeni_xin]

[Quote=引用 17 楼 fa_ge 的回复:]
引用 9 楼 phoeni_xin 的回复:
hextoint

inttohex

居然还是int类型的。。


楼主是用DELPHI的吧，Ado把它取出来，用IntToHex(x,2)转下就好了
[/Quote]

不是。用C#的。

主要是看数据库内部能否实现。不能的话。再考虑前台。

[chuifengde]

楼主，对你无语了，每层楼给的代码你都认真的测了吗？13楼的不行？

[jenny0810]

进来学习

[phoeni_xin]

[Quote=引用 13 楼 chuifengde 的回复:]
SELECT stuff(master.dbo.fn_varbintohexstr(cast(cast(281474976710655 AS BIGINT) as varbinary(6))),1,2,'')

[/Quote]

恩。这个是可以的。能改下成FF-FF-FF-FF-FF-FF的格式的吗？

[振乾]

进制之间的转换 学习了

[振乾]

[Quote=引用 6 楼 chuifengde 的回复:]
SQL code
SELECT cast(cast(281474976710655 AS BIGINT) as varbinary(6))
--result
/*0xFFFFFFFFFFFF*/
[/Quote]

这个很对。

[lzy5710303]

太不容易了，终于找到了
master.dbo.fn_varbintohexstr是个不常用的系统函数