--直接转确实不行declare @name varchar(30),@sql nvarchar(300),@i int set @name=(select name from t2 where num=990) set @sql=N'set @i=convert(int,0x'+@name+')' exec sp_executesql @sql,N'@i int output',@i outputselect @name as 'string', @i as 'int'
另外一个直观的例子: declare @i varchar(30),@j int,@sql nvarchar(300) set @i='abcd' set @i='0x'+@i set @sql=N'set @j=(select convert(int,'+@i+'))' print @sql exec sp_executesql @sql,N'@j int output',@j output select @i,@j
我的方法确实不行,编一个函数吧:CREATE FUNCTION Hex2Int8 (@Hex varchar(30)) RETURNS Bigint AS BEGIN DECLARE @Int8 Bigint DECLARE @CurChr varchar(1) DECLARE @CurInt int DECLARE @i int SET @Int8= 0 SET @i= 1 while @i<=len(@Hex) BEGIN SET @CurChr=substring(@Hex,@i,1) SET @CurInt=ASCII(@CurChr)
IF @CurInt>=ASCII('0') AND @CurInt>=ASCII('9') SET @CurInt=@CurInt-ASCII('0') ELSE IF @CurInt>=ASCII('a') AND @CurInt>=ASCII('f') SET @CurInt=@CurInt-ASCII('a')+10 ELSE IF @CurInt>=ASCII('A') AND @CurInt>=ASCII('F') SET @CurInt=@CurInt-ASCII('A')+10 ELSE BEGIN RETURN(-1) END SET @Int8= @Int8*16+@CurInt SET @i=@i+1 END RETURN(@Int8) END调用: select dbo.Hex2Int8(fieldname) as hex from tablename
convert(bigint,field1)
or
convert(int,field1)
set @name=(select name from t2 where num=990)
set @sql=N'set @i=convert(int,0x'+@name+')'
exec sp_executesql @sql,N'@i int output',@i outputselect @name as 'string', @i as 'int'
declare @i varchar(30),@j int,@sql nvarchar(300)
set @i='abcd'
set @i='0x'+@i
set @sql=N'set @j=(select convert(int,'+@i+'))'
print @sql
exec sp_executesql @sql,N'@j int output',@j output
select @i,@j
RETURNS Bigint
AS
BEGIN
DECLARE @Int8 Bigint
DECLARE @CurChr varchar(1)
DECLARE @CurInt int
DECLARE @i int SET @Int8= 0
SET @i= 1
while @i<=len(@Hex)
BEGIN
SET @CurChr=substring(@Hex,@i,1)
SET @CurInt=ASCII(@CurChr)
IF @CurInt>=ASCII('0') AND @CurInt>=ASCII('9')
SET @CurInt=@CurInt-ASCII('0')
ELSE
IF @CurInt>=ASCII('a') AND @CurInt>=ASCII('f')
SET @CurInt=@CurInt-ASCII('a')+10
ELSE
IF @CurInt>=ASCII('A') AND @CurInt>=ASCII('F')
SET @CurInt=@CurInt-ASCII('A')+10
ELSE
BEGIN
RETURN(-1)
END
SET @Int8= @Int8*16+@CurInt
SET @i=@i+1
END RETURN(@Int8)
END调用:
select dbo.Hex2Int8(fieldname) as hex from tablename