CREATE FUNCTION dbo.f_hex_dec(@s varchar(32))
RETURNS bigint
AS
BEGIN
DECLARE @i bigint,@result bigint
SELECT @i=0,@result=0,@s=RTRIM(LTRIM(UPPER(REVERSE(@s))))
WHILE @i<LEN(@s)
BEGIN
IF SUBSTRING(@s,@i+1,1) not between '0' and '9' and SUBSTRING(@s,@i+1,1) not between 'A' and 'F'
BEGIN
SELECT @result=0
break
END
SELECT @result=@result+(CHARINDEX(SUBSTRING(@s,@i+1,1),'0123456789ABCDEF')-1)*POWER(16,@i),@i=@i+1
END
RETURN @result
END
GO调用函数运行:select oid from lists where oid=dbo.f_hex_dec('7a7a003500bc0001') and state=0 and tid=13出现错误为:类型 int 发生算术溢出错误,值 = 4294967296.000000。
为什么呢?RETURNS为bigint,@i bigint,@result bigint都为bigint,这些来存储16个16进制数应该没问题啊?请知道者帮忙修改下代码?谢谢了
RETURNS bigint
AS
BEGIN
DECLARE @i bigint,@result bigint
SELECT @i=0,@result=0,@s=RTRIM(LTRIM(UPPER(REVERSE(@s))))
WHILE @i<LEN(@s)
BEGIN
IF SUBSTRING(@s,@i+1,1) not between '0' and '9' and SUBSTRING(@s,@i+1,1) not between 'A' and 'F'
BEGIN
SELECT @result=0
break
END
SELECT @result=@result+(CHARINDEX(SUBSTRING(@s,@i+1,1),'0123456789ABCDEF')-1)*POWER(16,@i),@i=@i+1
END
RETURN @result
END
GO调用函数运行:select oid from lists where oid=dbo.f_hex_dec('7a7a003500bc0001') and state=0 and tid=13出现错误为:类型 int 发生算术溢出错误,值 = 4294967296.000000。
为什么呢?RETURNS为bigint,@i bigint,@result bigint都为bigint,这些来存储16个16进制数应该没问题啊?请知道者帮忙修改下代码?谢谢了
楼主给的输入,转换为10进制后,已超过这个数了.
bigint:从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据,存储大小为 8 个字节。一个字节就是8位,那么bigint就有64位int:从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据,存储大小为 4 个字节。int类型,最大可以存储32位的数据smallint:从-2^15(-32,768)到2^15-1(32,767)的整数数据,存储大小为 2 个字节。smallint就是有16位tinyint:从0到255的整数数据,存储大小为 1 字节。tinyint就有8位。
/*
* @Param : @strType,@strNum
* @Author : ICE
* Date : 2009/03/16
* Description :
* 十六进制转10进制
select dbo.fn_OtherConvertDec('0123456789ABCDE','1D')
八进制转10进制
select dbo.fn_OtherConvertDec('01234567','11')
返回-1表示输入的参数有问题
*/CREATE FUNCTION dbo.fn_OtherConvertDec
(
@strType varchar(100) = '', --从低位到高位的进制表示字符
@strNum varchar(100) = ''
)
RETURNS INT
AS
BEGIN
DECLARE @_nLen INT,
@_nResoult INT,
@_nFlag INT,
@_nTemp INT,
@_nSeqNo INT SET @_nLen = 0
SET @_nResoult = 0
SET @_nFlag = 0
SET @_nTemp = 0
SET @_nSeqNo = 1 IF ISNULL(@strType,'') = '' OR ISNULL(@strNum,'') = ''
BEGIN
RETURN -1
END DECLARE @Table TABLE
(
NUM VARCHAR(50)
)
WHILE @_nSeqNo <= LEN(@strType)
BEGIN
INSERT INTO @Table(NUM)
SELECT SUBSTRING(@strType,@_nSeqNo,1)
SET @_nSeqNo = @_nSeqNo + 1
END IF (SELECT COUNT(DISTINCT NUM) FROM @Table) <> LEN(@strType)
BEGIN
RETURN -1
END
SET @_nLen = LEN(@strNum)
WHILE @_nFlag < @_nLen
BEGIN
SELECT @_nTemp = CHARINDEX(SUBSTRING(@strNum,@_nFlag+1,1),@strType) - 1
IF @_nTemp < 0
BEGIN
RETURN -1
END
SELECT @_nResoult = (@_nTemp)* POWER(LEN(@strType),@_nLen-@_nFlag-1) + @_nResoult
SET @_nFlag = @_nFlag + 1
END
RETURN @_nResoult
END
DECLARE @i BIGINT
declare @result BIGINT
DECLARE @n VARCHAR(MAX)
DECLARE @m VARCHAR(MAX)SET @s = '7a7a003500bc0001'
SELECT @i = 0 ,
@result = 0 ,
@s = RTRIM(LTRIM(UPPER(REVERSE(@s))))
WHILE @i < DATALENGTH(@s)
BEGIN
IF SUBSTRING(@s, @i + 1, 1) NOT BETWEEN '0' AND '9'
AND SUBSTRING(@s, @i + 1, 1) NOT BETWEEN 'A' AND 'F'
BEGIN
SELECT @result = 0
BREAK
END
PRINT '@s值:'+@s
SELECT @m = CONVERT(VARCHAR,@i)
PRINT '@i值:'+@m
SELECT @result = @result + ( CHARINDEX(SUBSTRING(@s, @i + 1, 1),
'0123456789ABCDEF') - 1 )
* POWER(16, @i) ,
@i = @i + 1
SELECT @n = CONVERT(VARCHAR,@result)
PRINT '@result值:'+@n
END /*
@s值:1000CB005300A7A7
@i值:0
@result值:1
@s值:1000CB005300A7A7
@i值:1
@result值:1
@s值:1000CB005300A7A7
@i值:2
@result值:1
@s值:1000CB005300A7A7
@i值:3
@result值:1
@s值:1000CB005300A7A7
@i值:4
@result值:786433
@s值:1000CB005300A7A7
@i值:5
@result值:12320769
@s值:1000CB005300A7A7
@i值:6
@result值:12320769
@s值:1000CB005300A7A7
@i值:7
@result值:12320769
@s值:1000CB005300A7A7
@i值:8
消息 232,级别 16,状态 3,第 25 行
类型 int 发生算术溢出错误,值 = 4294967296.000000。*/
SELECT (CHARINDEX(SUBSTRING('1000CB005300A7A7', 9, 1),'0123456789ABCDEF') - 1) --5
SELECT POWER(16, 9) --16的9次幂,超过了int的大小哦。--所以LZ的计算出现了报错。并不是bigint闹的。是这个power函数的问题。
RETURNS bigint
AS
BEGIN
DECLARE @i BIGINT ,
@result BIGINT
DECLARE @data BIGINT --添加了这个
SET @data =16
SELECT @i = 0 ,
@result = 0 ,
@s = RTRIM(LTRIM(UPPER(REVERSE(@s))))
WHILE @i < DATALENGTH(@s)
BEGIN
IF SUBSTRING(@s, @i + 1, 1) NOT BETWEEN '0' AND '9'
AND SUBSTRING(@s, @i + 1, 1) NOT BETWEEN 'A' AND 'F'
BEGIN
SELECT @result = 0
BREAK
END
SELECT @result = @result + ( CHARINDEX(SUBSTRING(@s, @i + 1, 1),
'0123456789ABCDEF') - 1 )
* POWER(@data, @i) ,
@i = @i + 1
END
RETURN @result
END SELECT dbo.f_hex_dec('7a7a003500bc0001')--8825366647431495681
7a7a003500bc0001转换为十进制后远远大于bigint的表示范围,
8825366647431495681>4294967296