--请参考这个存储过程,可以将代表十六进制数的字型串变为十进制数字 --相反的转换你自己完成。这是方法一 create proc C16_To_N10 @string char(10), @value int output as declare @long intset @value = 0 set @string = ltrim(@string) set @string = right(rtrim(@string),len(rtrim(@string))-2) set @string = rtrim(ltrim(upper(reverse(@string)))) --为方便计算,将字符串反向 set @long = len(rtrim(@string))while @long > 0 Begin set @value = @value + case when substring(@string,@long,1) = '0' then 0 when substring(@string,@long,1) <= '9' then (unicode(substring(@string,@long,1)) - 48) * cast(power(16,@long-1) as bigint) else (unicode(substring(@string,@long,1)) - 55) * cast(power(16,@long-1) as bigint) end set @long = @long - 1 End
select @value = case when @value > 2147483648 then @value - 4294967296 else @value end --负数的转换方法
--方法二 create proc C16_To_N10_2 @string char(10), @value int output as declare @sql nchar(200) set @sql = 'if exists(select 1 from sysobjects where name = ''TMPTable'' and xtype = ''U'') drop table Tmptable select value = convert(varbinary,'+@string+') into TMPTable' exec(@sql) select @value=value from TMPTable drop table TMPTable
--十六进制字符转十进制数值方法三(排荐) create proc C16_To_N10_3 @string char(10), @value int output as declare @sql nchar(100) set @sql = 'set @valueTmp = convert(varbinary,0xb)' exec sp_executesql @sql, N'@valueTmp int output', @valueTmp = @value output
--不好意思,方法三修改一下: create function C16_To_N10_3 @string char(10), @value int output as declare @sql nchar(100) set @sql = 'set @valueTmp = convert(varbinary,'+@string+')' exec sp_executesql @sql, N'@valueTmp int output', @valueTmp = @value output
--相反的转换你自己完成。这是方法一
create proc C16_To_N10
@string char(10),
@value int output
as
declare @long intset @value = 0
set @string = ltrim(@string)
set @string = right(rtrim(@string),len(rtrim(@string))-2)
set @string = rtrim(ltrim(upper(reverse(@string)))) --为方便计算,将字符串反向
set @long = len(rtrim(@string))while @long > 0
Begin
set @value = @value + case
when substring(@string,@long,1) = '0' then 0
when substring(@string,@long,1) <= '9'
then (unicode(substring(@string,@long,1)) - 48) * cast(power(16,@long-1) as bigint)
else (unicode(substring(@string,@long,1)) - 55) * cast(power(16,@long-1) as bigint)
end
set @long = @long - 1
End
select @value = case
when @value > 2147483648 then @value - 4294967296
else @value end --负数的转换方法
SELECT @vS=dbo.fn_varbintohexstr(0x73f)
PRINT @vS
create proc C16_To_N10_2
@string char(10),
@value int output
as
declare @sql nchar(200)
set @sql = 'if exists(select 1
from sysobjects
where name = ''TMPTable'' and xtype = ''U'')
drop table Tmptable
select value = convert(varbinary,'+@string+')
into TMPTable'
exec(@sql)
select @value=value from TMPTable
drop table TMPTable
create proc C16_To_N10_3
@string char(10),
@value int output
as
declare @sql nchar(100)
set @sql = 'set @valueTmp = convert(varbinary,0xb)'
exec sp_executesql @sql,
N'@valueTmp int output',
@valueTmp = @value output
create function C16_To_N10_3
@string char(10),
@value int output
as
declare @sql nchar(100)
set @sql = 'set @valueTmp = convert(varbinary,'+@string+')'
exec sp_executesql @sql,
N'@valueTmp int output',
@valueTmp = @value output
但是转换出来的是字符的ASC码
并不是我要的数值
555555。
declare @string char(10)
declare @value int
declare @sql nchar(100) set @string = '0x1b' --设16进制字符串set @sql = 'set @valueTmp = convert(varbinary,'+@string+')'
exec sp_executesql @sql,
N'@valueTmp int output',
@valueTmp = @value output
select @value-- 27