sql server里将十六进制转换成十进制

在网上找了很多，总是不知道怎么用，于是自己写了一个：

 

declare @strHex char(5), @len int, @intOut int, @i int, @charint int set @strHex = '20' set @len = len(rtrim(@strHex)) set @i = 1 set @intOut = 0 while @i <= @len begin set @charint = case substring(upper(rtrim(ltrim(@strHex))),@i,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substring(@strHex,@i,1) end set @intOut = @intOut +@charint * power(16, @len-@i) set @i = @i + 1 end print @intOut 

 

由于要批量修改，帮写成了游标的形式：

 

use pubs declare @strHex char(5), @len int, @intOut int, @i int, @charint int, @keyname char(50) declare hextoint cursor for select rtrim(col001),rtrim(col002) from vv open hextoint fetch next from hextoint into @keyname,@strHex while @@fetch_status =0 begin set @len = len(rtrim(@strHex)) set @i = 1 set @intOut = 0 while @i <= @len begin set @charint = case substring(upper(rtrim(ltrim(@strHex))),@i,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substring(@strHex,@i,1) end set @intOut = @intOut +@charint * power(16, @len-@i) set @i = @i + 1 end update vv set col002 = @intout where col001 = rtrim(@keyname) --print @intOut fetch next from hextoint into @keyname,@strHex end close hextoint deallocate hextoint 

 

记以下留作以后参考吧。