to:
Jianli2004(健力)
是啊!!
我只是说从技术的角度来说啊!!
大家看看能不能突破SQL38位有效数字的限制!!
Jianli2004(健力)
是啊!!
我只是说从技术的角度来说啊!!
大家看看能不能突破SQL38位有效数字的限制!!
解决方案 »
- 求Sql 语句 时间段的合并
- 末位不能是4的序列号该怎么算?
- 现在真正高效的存储过程是哪种写法?
- 求一SQL语句(有些难度)
- 求一sql语句,在线等待!急!!
- SQL语句如何选取表的最后十条记录?
- 请问VFP小问题。
- 请问在MS SQL Server7.0中,怎样才能强行对一个自增字段进行编辑?
- 请大家来帮帮忙!!!
- 高分!————如何将查询到的字段加1同时再存储到原来的表里 在线!!!!
- 这个动态sql语句错在哪啊,老通不过,谢谢了SET @strSQL='SELECT @int_allid + =count(*) FROM ForumDis WHERE F_SubNo =@ intSubNo '
- 我如何把数据里的内容变成我所需要的格式!!!!!!!!!
限制是没法破的。
只能用字符串,然后分断用数值处理。
88888888……8777777……7
.
88888888……
8777777…
12345678
比如:取最后8位“12345678”
12345678/1000=12345.678
-->保留两位小数点:12345.68
-->12345.68*100=1234568 跟据结果加到上面的字符串88888888……8777777……7
.
88888888……
8777777…
1234568
怎么样才能以38位划分段,来循环呢?
zjcxc(邹建)
帮忙!!
多谢!!
--select dbo.Opprecision('234343234.23443',10000)drop function OpPrecision
go
create function OpPrecision(@data varchar(8000),@precision int)
returns varchar(8000)
as
begin
declare @rtn varchar(8000),@leftstr varchar(500),@rightstr varchar(500)
declare @len int,@i int, @quoteindex int declare @temprightstr varchar(8000)
declare @rightlen int
declare @tempstr varchar(4)
select @len=len(@data),@i=1,@quoteindex=charindex('.',@data)
if @quoteindex=0
begin
select @leftstr=@data,@rightstr=''
end else
begin
select @leftstr=left(@data,@quoteindex-1),@rightstr=right(@data,@len-isnull(@quoteindex,@len))
end
select @rightlen=len(@rightstr)
select @temprightstr='' while @i<=@precision
begin
if @rightlen<=@precision
begin
if @i<=@rightlen
begin
select @temprightstr=@temprightstr+substring(@rightstr,@i,1)
end else
begin
select @temprightstr=@temprightstr+'0'
end
end else
begin
if @i=@precision
begin
select @tempstr=substring(@rightstr,@i+1,1)
if str(@tempstr)>=5
begin
select @temprightstr=ltrim(str(@temprightstr+substring(@rightstr,@i,1))+1)
end else
begin
select @temprightstr=@temprightstr+substring(@rightstr,@i,1)
end
end else
begin
select @temprightstr=@temprightstr+substring(@rightstr,@i,1)
end
end
select @i=@i+1
end
select @rtn=@leftstr+'.'+@temprightstr
return (@rtn)
end
go
多谢!!
select dbo.Opprecision('23434387689.975746464858686',10)
服务器: 消息 248,级别 16,状态 1,过程 OpPrecision,行 41
varchar 值 '9757464648' 的转换溢出了 int 列。超出了最大整数值。
函数中的这行有问题:
select @temprightstr=ltrim(str(@temprightstr+substring(@rightstr,@i,1))+1)
select @temprightstr=@temprightstr+ltrim(str(substring(@rightstr,@i,1))+1)
但还有问题:
select dbo.Opprecision('23434387689.9999999999999',9)
---------------------
23434387689.9999999910
go
create function OpPrecision(@data varchar(8000),@precision int)
returns varchar(8000)
as
begin
declare @rtn varchar(8000),@leftstr varchar(500),@rightstr varchar(500)
declare @len int,@i int, @quoteindex int declare @temprightstr varchar(8000)
declare @rightlen int
declare @tempstr varchar(4)
select @len=len(@data),@i=1,@quoteindex=charindex('.',@data)
if @quoteindex=0
begin
select @leftstr=@data,@rightstr=''
end else
begin
select @leftstr=left(@data,@quoteindex-1),@rightstr=right(@data,@len-isnull(@quoteindex,@len))
end
select @rightlen=len(@rightstr)
select @temprightstr='' while @i<=@precision
begin
if @rightlen<=@precision
begin
if @i<=@rightlen
begin
select @temprightstr=@temprightstr+substring(@rightstr,@i,1)
end else
begin
select @temprightstr=@temprightstr+'0'
end
end else
begin
if @i=@precision
begin
select @tempstr=substring(@rightstr,@i+1,1)
if str(@tempstr)>=5
begin
declare @rightmovestr varchar(8000)
declare @leftmovestr varchar(8000),@swapstr varchar(2)
declare @j int
declare @isadd bit
select @j=@i,@rightmovestr='',@leftmovestr='',@isadd=1
while @j>0
begin
if str(substring(@rightstr,@j,1))+1=10
begin
select @rightmovestr='0'+@rightmovestr,@isadd=1
set @j=@j-1
end else
begin
select @rightmovestr=left(@rightstr,@j-1)+ltrim(str(substring(@rightstr,@j,1))+1)+@rightmovestr
select @j=0,@isadd=0
end
end
select @temprightstr=@rightmovestr
if @isadd=1
begin
select @j=len(@leftstr)
while @j>0
begin
if str(substring(@leftstr,@j,1))+1=10
begin
select @leftmovestr='0'+@leftmovestr
set @j=@j-1
end else
begin
select @leftmovestr=left(@leftstr,@j-1)+ltrim(str(substring(@leftstr,@j,1))+1)+@leftmovestr
select @j=0,@isadd=0
end
end
if @isadd=1
select @leftstr='1'+@leftmovestr
else
select @leftstr=@leftmovestr
end
end else
begin
select @temprightstr=@temprightstr+substring(@rightstr,@i,1)
end
end else
begin
select @temprightstr=@temprightstr+substring(@rightstr,@i,1)
end
end
select @i=@i+1
end
select @rtn=@leftstr+'.'+@temprightstr
return (@rtn)
end
go
OpenVMS(半知半解)
利用数字字符串啊!!
我只是说从技术的角度啊,实际上不可能有这么大的数字啊
FreeLong(Long&Men) if str(substring(@rightstr,@j,1))+1=10
可以改为:
if substring(@rightstr,@j,1)+1=10
吧同理:
if str(substring(@leftstr,@j,1))+1=10
也可以改为:
if substring(@leftstr,@j,1)+1=10多谢您的帮助!!
FreeLong(Long&Men)
能加上这样的功能吗:
select dbo.Opprecision('6899.99999999999999999',-2)
------------------
6900
select dbo.Opprecision('6899.99999999999999999',-1)
------------------
6900
go
create function OpPrecision(@data varchar(8000),@precision int)
returns varchar(8000)
/***********************************************************************************
Name: OpPrecision
Parameters: @data 数据 @precision 精度
Return: 数字字符串
Descr: 返回数字表达式并四舍五入为指定的长度或精度(精度比Round大)
Date: 2004-1-4
Example: select dbo.Opprecision('39989.899999999999999999999999999',3)
***********************************************************************************/
as
begin
-------------------------------------------------------------------------------
--Declare Variant
declare @rtn varchar(8000),@leftstr varchar(500),@rightstr varchar(500)
declare @len int,@i int, @quoteindex int
declare @temprightstr varchar(8000),@templeftstr varchar(8000)
declare @rightlen int
declare @tempstr varchar(4)
-------------------------------------------------------------------------------
--Quoter String
select @len=len(@data),@i=1,@quoteindex=charindex('.',@data)
if @quoteindex=0
begin
select @leftstr=@data,@rightstr=''
end else
begin
select @leftstr=left(@data,@quoteindex-1),@rightstr=right(@data,@len-isnull(@quoteindex,@len))
end
select @rightlen=len(@rightstr)
select @temprightstr='',@templeftstr=''
-------------------------------------------------------------------------------
/*精度小于0时*/
if @precision<0
begin
/*整数部分长度小于精度长度*/
if len(@leftstr)<abs(@precision)
select @leftstr='0'
else
/*整数部分长度等于精度长度*/
if len(@leftstr)=abs(@precision)
begin
/*第一位数字大于等于5*/
if substring(@leftstr,1,1)>=5
begin
set @leftstr=''
while @i<=abs(@precision)
begin
select @leftstr=@leftstr+'0'
set @i=@i+1
end
select @leftstr='1'+@leftstr
end else
/*第一位数字大于小于5*/
select @leftstr='0'
end else
/*整数部分长度大于精度长度*/
begin
/*补零*/
while @i<=abs(@precision)
begin
select @templeftstr=@templeftstr+'0'
set @i=@i+1
end
/*精度位大于5*/
if substring(@leftstr,len(@leftstr)-abs(@precision)+1,1)>=5
begin
/*精度位前一位加1等于10*/
if substring(@leftstr,len(@leftstr)-abs(@precision),1)+1=10
begin
declare @w int
declare @isdel bit
select @isdel=1,@w=len(@leftstr)-abs(@precision)
while @w>0
begin
if substring(@leftstr,@w,1)+1=10
begin
select @templeftstr='0'+@templeftstr
set @w=@w-1
end else
begin
select @templeftstr=left(@leftstr,@w-1)+ltrim(str(substring(@leftstr,@w,1))+1)+@templeftstr
select @w=0,@isdel=0
end
end
if @isdel=1
select @leftstr='1'+@templeftstr
else
select @leftstr=@templeftstr
end else
/*精度位前一位加1小于10*/
begin
select @templeftstr=ltrim(str(substring(@leftstr,len(@leftstr)-abs(@precision),1))+1)+@templeftstr
select @leftstr=left(@leftstr,len(@leftstr)-abs(@precision)-1)+@templeftstr
end
end
end
end else
/*精度大于0*/
begin
while @i<=@precision
begin
/*小数部分长度小于等于精度长度*/
if @rightlen<=@precision
begin
if @i<=@rightlen
select @temprightstr=@temprightstr+substring(@rightstr,@i,1)
else
select @temprightstr=@temprightstr+'0'
end else
begin
/*处理进位*/
if @i=@precision
begin
select @tempstr=substring(@rightstr,@i+1,1)
/*精度位大于5前一位需要加1*/
if str(@tempstr)>=5
begin
declare @rightmovestr varchar(8000)
declare @leftmovestr varchar(8000),@swapstr varchar(2)
declare @j int
declare @isadd bit
select @j=@i,@rightmovestr='',@leftmovestr='',@isadd=1
while @j>0
begin
/*前一位加1等于10需要进位*/
if substring(@rightstr,@j,1)+1=10
begin
select @rightmovestr='0'+@rightmovestr,@isadd=1
set @j=@j-1
end else
begin
select @rightmovestr=left(@rightstr,@j-1)+ltrim(str(substring(@rightstr,@j,1))+1)+@rightmovestr
select @j=0,@isadd=0
end
end
/*设置小数部分*/
select @temprightstr=@rightmovestr
/*小数部分为零需要进位整数部分*/
if @isadd=1
begin
select @j=len(@leftstr)
while @j>0
begin
if substring(@leftstr,@j,1)+1=10
begin
select @leftmovestr='0'+@leftmovestr
set @j=@j-1
end else
begin
select @leftmovestr=left(@leftstr,@j-1)+ltrim(str(substring(@leftstr,@j,1))+1)+@leftmovestr
select @j=0,@isadd=0
end
end
if @isadd=1
select @leftstr='1'+@leftmovestr
else
select @leftstr=@leftmovestr
end
end else /*设置整数部分*/
select @temprightstr=@temprightstr+substring(@rightstr,@i,1)
end else
select @temprightstr=@temprightstr+substring(@rightstr,@i,1)
end
select @i=@i+1
end
end
-------------------------------------------------------------------------------
/*设置返回字符串*/
if len(@temprightstr)>0
select @rtn=@leftstr+'.'+@temprightstr
else
select @rtn=@leftstr
/*返回结果*/
return (@rtn)
endgo
修改他的函数,使得能处理非数字字符串。
请FreeLong(面朝大海-春暖花开) 指正!!谢谢create function OpPrecision(@data varchar(8000),@precision int)
returns varchar(8000)
/***********************************************************************************
Name: OpPrecision
Parameters: @data 数据 @precision 精度
Return: 数字字符串
Descr: 返回数字表达式并四舍五入为指定的长度或精度(精度比Round大)
Date: 2004-1-4
Example: select dbo.Opprecision('39989.899999999999999999999999999',3)
***********************************************************************************/
as
begin
declare @result varchar(8000),@leftstr varchar(8000),@rightstr varchar(8000)
declare @len int,@quoteindex int,@leftlen int,@rightlen int,@i int,@j int,@iscarry bit
declare @templeftstr varchar(8000),@temprightstr varchar(8000)
/*NULL、空''或空格' '不处理*/
if (@data is null) or (@data='')
begin
set @result=@data
end
else
begin
select @data=rtrim(@data),@len=len(@data),@quoteindex=charindex('.',@data)
if @quoteindex=0
begin
select @leftstr=@data,@rightstr=''
end
else
begin
select @leftstr=left(@data,@quoteindex-1),@rightstr=right(@data,@len-@quoteindex)
end
select @leftlen=len(@leftstr),@rightlen=len(@rightstr)
select @templeftstr=@leftstr,@temprightstr=''
-------------------------------------------------------------------------------
/*精度小于0时*/
if @precision<0
begin
set @precision=abs(@precision)
/*整数部分长度小于精度长度*/
if @leftlen<@precision
select @templeftstr='0'
/*整数部分长度等于精度长度*/
if @leftlen=@precision
begin
select @templeftstr=case when substring(@leftstr,1,1) like '[0-9]' then case when substring(@leftstr,1,1)>=5 then '1'+replicate('0',@precision) else '0' end else @leftstr end
end
/*整数部分长度大于精度长度*/
if @leftlen>@precision
begin
/*补零*/
select @templeftstr=replicate('0',@precision)
/*精度位大于等于5前一位需要加1*/
if (substring(@leftstr,@leftlen-@precision+1,1) like '[0-9]') and (substring(@leftstr,@leftlen-@precision+1,1)>=5)
begin
/*精度位前一位加1等于10需要进位*/
if (substring(@leftstr,@leftlen-@precision,1) like '[0-9]') and (substring(@leftstr,@leftlen-@precision,1)+1=10)
begin
select @j=@leftlen-@precision,@iscarry=1
while @j>0
begin
if (substring(@leftstr,@j,1) like '[0-9]') and (substring(@leftstr,@j,1)+1=10)
begin
select @templeftstr='0'+@templeftstr
set @j=@j-1
end
else
begin
select @templeftstr=left(@leftstr,@j-1)+case when substring(@leftstr,@j,1) like '[0-9]' then cast((substring(@leftstr,@j,1)+1) as varchar(2)) else substring(@leftstr,@j,1) end+@templeftstr
select @j=0,@iscarry=0
end
end
if @iscarry=1
select @templeftstr='1'+@templeftstr
end
/*精度位前一位加1小于10*/
else
begin
select @templeftstr=left(@leftstr,@leftlen-@precision-1)+case when substring(@leftstr,@leftlen-@precision,1) like '[0-9]' then cast((substring(@leftstr,@leftlen-@precision,1)+1) as varchar(2)) else substring(@leftstr,@leftlen-@precision,1) end+@templeftstr
end
end
/*精度位小于5*/
else
select @templeftstr=left(@leftstr,@leftlen-@precision)+@templeftstr
end
end
/*精度大于等于0时*/
else
begin
set @precision=case when @precision>8000 then 8000 else @precision end
/*小数部分长度小于等于精度长度*/
if @rightlen<=@precision
begin
select @temprightstr=@rightstr+replicate('0',@precision-@rightlen)
end
/*小数部分长度大于精度长度*/
else
begin
set @i=0
while @i<=@precision
begin
/*处理进位*/
if @i=@precision
begin
/*精度位大于等于5前一位需要加1*/
if (substring(@rightstr,@i+1,1) like '[0-9]') and (substring(@rightstr,@i+1,1)>=5)
begin
select @j=@i,@iscarry=1,@temprightstr=''
while @j>0
begin
/*加1等于10需要进位*/
if (substring(@rightstr,@j,1) like '[0-9]') and (substring(@rightstr,@j,1)+1=10)
begin
select @temprightstr='0'+@temprightstr
set @j=@j-1
end
/*加1小于10*/
else
begin
select @temprightstr=left(@rightstr,@j-1)+case when substring(@rightstr,@j,1) like '[0-9]' then cast((substring(@rightstr,@j,1)+1) as varchar(2)) else substring(@rightstr,@j,1) end+@temprightstr
select @j=0,@iscarry=0
end
end
/*需要进位整数部分*/
if @iscarry=1
begin
select @j=@leftlen,@templeftstr=''
while @j>0
begin
/*加1等于10需要进位*/
if (substring(@leftstr,@j,1) like '[0-9]') and (substring(@leftstr,@j,1)+1=10)
begin
select @templeftstr='0'+@templeftstr
set @j=@j-1
end
/*加1小于10*/
else
begin
select @templeftstr=left(@leftstr,@j-1)+case when substring(@leftstr,@j,1) like '[0-9]' then cast((substring(@leftstr,@j,1)+1) as varchar(2)) else substring(@leftstr,@j,1) end+@templeftstr
select @j=0,@iscarry=0
end
end
if @iscarry=1
select @templeftstr='1'+@templeftstr
end
end
/*精度位小于5*/
else
begin
select @temprightstr=@temprightstr+substring(@rightstr,@i,1)
end
end
/*非进位时*/
else
begin
select @temprightstr=@temprightstr+substring(@rightstr,@i,1)
end
select @i=@i+1
end
end
end
select @result=@templeftstr + case @temprightstr when '' then '' else '.'+@temprightstr end
end
return (@result)
end
goprint dbo.Opprecision('56999.999999999',6)
print dbo.Opprecision('56999.999x99999',13)
print dbo.Opprecision('56999.999999999',0)
print dbo.Opprecision('a56999.999999999',-6)
print dbo.Opprecision('56999.999999999',-4)
print dbo.Opprecision('56999.999999999',-5)
print dbo.Opprecision('56999.999999999',-8)go
drop function OpPrecision
FreeLong(面朝大海-春暖花开)
我也不是很清楚,您知道通常处理方法吗?