【原创】人民币小写金额转大写 问:比 money 类型上限值大的数值怎么办? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 这是以前收藏的..exec ConvertToUpperCase 1000600200100.56,@a output --将123456789.56转换成大写金额select @a --在QA中查看效果create procedure ConvertToUpperCase(@mExpense Money,@sRetUpperMoney varchar(50) OutPut)asbegindeclare @sExpense varchar(20),@sLittle varchar(4),@sFixed varchar(15)declare @nDot Integer,@nLenAll Integer,@sUpperMoney varchar(30),@nStep Integerdeclare @cChar char(1),@sUpperNumbers varchar(30),@sNumber char(2),@sTemp varchar(50)declare @sZero char(2),@nIsZeroLast Integer,@sth varchar(10),@nFixed Integerdeclare @sMoney char(2)if @mExpense=0beginselect @sRetUpperMoney='零元'returnendif @mExpense<0beginselect @sMoney='负'select @mExpense=abs(@mExpense)endelse select @sMoney=''select @sUpperMoney = '万仟佰拾亿仟佰拾万仟佰拾元' --加大范围select @sUpperNumbers= '玖捌柒陆伍肆叁贰壹零'select @sZero = '零'select @sExpense = convert(varchar(20),@mExpense)select @nDot = CharIndex('.',@sExpense)select @nLenAll = Len(@sExpense)if @nDot > 0beginselect @sFixed = substring(@sExpense,1,@nDot -1)select @sLittle = substring(@sExpense,@nDot + 1,@nLenAll - @nDot)endelseselect @sFixed = @sExpense select @nFixed = Len(@sFixed) select @nStep = @nFixed select @sTemp = ''select @nIsZeroLast = 0if @sFixed <> '0'while @nStep > 0begin --while @nStep > 0select @cChar = substring(@sFixed,@nStep,1) select @sNumber = substring(@sUpperNumbers,10 - convert(int,@cChar),1) select @sth = substring(@sUpperMoney,len(@sUpperMoney) - (Len(@sFixed) - @nStep),1) if @cChar <> '0'begin --if @cChar <> '0'if @nIsZeroLast = 0select @sTemp = @sNumber + @sth + @sTemp if @nIsZeroLast = 1beginselect @sTemp = @sNumber + @sth + @sZero + @sTempselect @nIsZeroLast = 0endif @nIsZeroLast = 2beginselect @sTemp = @sNumber + @sth + @sTempselect @nIsZeroLast = 0endend --if @cChar <> '0'else --@cChar = '0'beginif @nIsZeroLast <> 2begin --@nIsZeroLast <> 2if (@sth = '万') or (@sth = '亿') or (@sth = '元')begin if @nIsZeroLast = 1beginselect @sTemp = @sth + @sZero + @sTempif substring(@sTemp,1,3)='亿万零' --修改select @sTemp=stuff(@sTemp,1,3,'亿零')select @nIsZeroLast = 2endelsebeginselect @sTemp = @sth + @sTempif @sTemp='万元' --修改select @sTemp='元'select @nIsZeroLast = 2endend elseselect @nIsZeroLast = 1end else --@nIsZeroLast = 2beginif (@sth = '仟') select @nIsZeroLast = 0endend --@nIsZeroLast <> 2select @nStep = @nStep - 1 end --while @nStep > 0declare @sTempLittle varchar(10),@sFirst char(1),@sSecond char(1)select @sFirst = '',@sSecond = ''if Len(@sLittle) = 2beginselect @sFirst = substring(@sLittle,1,1),@sSecond = substring(@sLittle,2,1)endif Len(@sLittle) = 1beginselect @sFirst = substring(@sLittle,1,1)endselect @sTempLittle = ''if (@sFirst <> '') and (@sFirst <> '0')beginselect @sNumber = substring(@sUpperNumbers,10 - convert(int,@sFirst),1)select @sTempLittle = @sTempLittle + @sNumber + '角'endif (@sSecond <> '') and (@sSecond <> '0')beginselect @sNumber = substring(@sUpperNumbers,10 - convert(int,@sSecond),1)select @sTempLittle = @sTempLittle + @sNumber + '分'endselect @sRetUpperMoney = @sMoney + @sTemp + @sTempLittleend 有些小问题:select dbo.fn_GetFormatMoney(1201001.10)--转换成:壹佰贰拾万壹仟零壹圆壹角整--要求为:壹佰贰拾万零壹仟零壹元壹角整 如果是 Web 应用,走一遍数据库用函数搞,性能不值得花费。还是用 javascript 来得爽。参见以下链接:js脚本_小写金额转换成大写(支持千分位格式及负数输入,整数部分可达44位-千正~元,小数部分可达5位-角~丝) AmountInWords(dValue, maxDec) select dbo.fn_GetFormatMoney(100001000)--转换成:壹亿壹仟圆整--要求为:壹亿零壹仟圆整 不知道LZ是否了解会计核算中对于金额的要求。应该是需要加零处理的,建议google下对于金额大写的规定,这样函数就更健壮了。学习了! 汗。以前用java写过用sql太笨重了吧 关于EXISTS的效率问题 SQLSERVER能用T-SQL修改标识列吗? 遇到奇怪问题了,sqlserver查询速度居然比access慢很多~ 关于xp_sendmail 求高手 在线等 一个多用户访问的问题 还是表数据纵横转换问题,求助 我想写一条判断对比日期的语句,请问语句该怎样写? 列名无效 如何提高sql server的查询速度. sql server 跨服务器操作 取不到表自增列
select @a --在QA中查看效果
create procedure ConvertToUpperCase
(@mExpense Money,@sRetUpperMoney varchar(50) OutPut)
as
begin
declare @sExpense varchar(20),@sLittle varchar(4),@sFixed varchar(15)
declare @nDot Integer,@nLenAll Integer,@sUpperMoney varchar(30),@nStep Integer
declare @cChar char(1),@sUpperNumbers varchar(30),@sNumber char(2),@sTemp varchar(50)
declare @sZero char(2),@nIsZeroLast Integer,@sth varchar(10),@nFixed Integer
declare @sMoney char(2)
if @mExpense=0
begin
select @sRetUpperMoney='零元'
return
end
if @mExpense<0
begin
select @sMoney='负'
select @mExpense=abs(@mExpense)
end
else select @sMoney=''
select @sUpperMoney = '万仟佰拾亿仟佰拾万仟佰拾元' --加大范围
select @sUpperNumbers= '玖捌柒陆伍肆叁贰壹零'
select @sZero = '零'
select @sExpense = convert(varchar(20),@mExpense)
select @nDot = CharIndex('.',@sExpense)
select @nLenAll = Len(@sExpense)
if @nDot > 0
begin
select @sFixed = substring(@sExpense,1,@nDot -1)
select @sLittle = substring(@sExpense,@nDot + 1,@nLenAll - @nDot)
end
else
select @sFixed = @sExpense select @nFixed = Len(@sFixed)
select @nStep = @nFixed select @sTemp = ''
select @nIsZeroLast = 0if @sFixed <> '0'
while @nStep > 0
begin --while @nStep > 0select @cChar = substring(@sFixed,@nStep,1)
select @sNumber = substring(@sUpperNumbers,10 - convert(int,@cChar),1)
select @sth = substring(@sUpperMoney,len(@sUpperMoney) - (Len(@sFixed) - @nStep),1) if @cChar <> '0'
begin --if @cChar <> '0'
if @nIsZeroLast = 0
select @sTemp = @sNumber + @sth + @sTemp
if @nIsZeroLast = 1
begin
select @sTemp = @sNumber + @sth + @sZero + @sTemp
select @nIsZeroLast = 0
end
if @nIsZeroLast = 2
begin
select @sTemp = @sNumber + @sth + @sTemp
select @nIsZeroLast = 0
end
end --if @cChar <> '0'else --@cChar = '0'begin
if @nIsZeroLast <> 2
begin --@nIsZeroLast <> 2
if (@sth = '万') or (@sth = '亿') or (@sth = '元')
begin
if @nIsZeroLast = 1
begin
select @sTemp = @sth + @sZero + @sTemp
if substring(@sTemp,1,3)='亿万零' --修改
select @sTemp=stuff(@sTemp,1,3,'亿零')
select @nIsZeroLast = 2
end
else
begin
select @sTemp = @sth + @sTemp
if @sTemp='万元' --修改
select @sTemp='元'
select @nIsZeroLast = 2
end
end elseselect @nIsZeroLast = 1
end else --@nIsZeroLast = 2begin
if (@sth = '仟')
select @nIsZeroLast = 0
end
end --@nIsZeroLast <> 2select @nStep = @nStep - 1 end --while @nStep > 0declare @sTempLittle varchar(10),@sFirst char(1),@sSecond char(1)
select @sFirst = '',@sSecond = ''
if Len(@sLittle) = 2
begin
select @sFirst = substring(@sLittle,1,1),@sSecond = substring(@sLittle,2,1)
endif Len(@sLittle) = 1
begin
select @sFirst = substring(@sLittle,1,1)
endselect @sTempLittle = ''
if (@sFirst <> '') and (@sFirst <> '0')
begin
select @sNumber = substring(@sUpperNumbers,10 - convert(int,@sFirst),1)
select @sTempLittle = @sTempLittle + @sNumber + '角'
end
if (@sSecond <> '') and (@sSecond <> '0')
begin
select @sNumber = substring(@sUpperNumbers,10 - convert(int,@sSecond),1)
select @sTempLittle = @sTempLittle + @sNumber + '分'
end
select @sRetUpperMoney = @sMoney + @sTemp + @sTempLittle
end
select dbo.fn_GetFormatMoney(1201001.10)
--转换成:壹佰贰拾万壹仟零壹圆壹角整
--要求为:壹佰贰拾万零壹仟零壹元壹角整
--转换成:壹亿壹仟圆整
--要求为:壹亿零壹仟圆整
以前用java写过
用sql太笨重了吧