为了能够在报告中直接输入大写金额，本人编写了这个SQL Server的函数. 复制以下代码到SQL Server 查询分析器中运行，生成自定义函数 usf_ChineseCost. 在查询语句中调用该函数就可以输出大写的金额了。 SQL Server 中文金额 大写金额 CREATE FUNCTION usf_Ch
为了能够在报告中直接输入大写金额，本人编写了这个SQL Server的函数. 复制以下代码到SQL Server 查询分析器中运行，生成自定义函数 usf_ChineseCost. 在查询语句中调用该函数就可以输出大写的金额了。
SQL Server 中文金额 大写金额 <style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms -ecms
- –> <!—ecms -ecms
- $velocityCount
–> <!—ecms -ecms
–>
CREATE FUNCTION usf_ChineseCost( @Cost float)RETURNS varchar(50)AS----大写钱数----BEGIN declare @returnStr varchar(50) if (@Cost >= 1000000000) Begin set @returnStr = '##########' return @returnStr end set @returnStr = '00000000000' set @returnStr = right(@returnStr + convert(varchar,convert(decimal(11,2),round(@cost,2))),12) set @returnStr = replace(@returnStr,'0','零') set @returnStr = replace(@returnStr,'1','壹') set @returnStr = replace(@returnStr,'2','贰') set @returnStr = replace(@returnStr,'3','叁') set @returnStr = replace(@returnStr,'4','肆') set @returnStr = replace(@returnStr,'5','伍') set @returnStr = replace(@returnStr,'6','陆') set @returnStr = replace(@returnStr,'7','柒') set @returnStr = replace(@returnStr,'8','捌') set @returnStr = replace(@returnStr,'9','玖') set @returnStr = @returnStr + '分' set @returnStr = stuff(@returnStr,len(@returnStr)-1,0,'角') set @returnStr = replace(@returnStr,'.','元') set @returnStr = stuff(@returnStr,len(@returnStr)-5,0,'拾') set @returnStr = stuff(@returnStr,len(@returnStr)-7,0,'百') set @returnStr = stuff(@returnStr,len(@returnStr)-9,0,'千') set @returnStr = stuff(@returnStr,len(@returnStr)-11,0,'万') set @returnStr = stuff(@returnStr,len(@returnStr)-13,0,'拾') set @returnStr = stuff(@returnStr,len(@returnStr)-15,0,'百') set @returnStr = stuff(@returnStr,len(@returnStr)-17,0,'千') set @returnStr = stuff(@returnStr,len(@returnStr)-19,0,'亿') set @returnStr = replace(@returnStr,'零亿','零') set @returnStr = replace(@returnStr,'零千','零') set @returnStr = replace(@returnStr,'零百','零') set @returnStr = replace(<strong>本文来源gaodaima#com搞(代@码$网6</strong>@returnStr,'零拾','零') set @returnStr = replace(@returnStr,'零零零','零') set @returnStr = replace(@returnStr,'零零','零') set @returnStr = replace(@returnStr,'零万','万') set @returnStr = replace(@returnStr,'零元','元') set @returnStr = replace(@returnStr,'零角','零') set @returnStr = replace(@returnStr,'零分','') while left(@returnStr,1) = '零' Begin set @returnStr = right(@returnStr,len(@returnStr)-1) end if ((left(@returnStr,1)='万')) begin set @returnStr = right(@returnStr,len(@returnStr)-1) end if ((left(@returnStr,1)='元')) begin set @returnStr = right(@returnStr,len(@returnStr)-1) end while left(@returnStr,1) = '零' Begin set @returnStr = right(@returnStr,len(@returnStr)-1) end while right(@returnStr,1) = '零' Begin set @returnStr = left(@returnStr,len(@returnStr)-1) end set @returnStr = replace(@returnStr,'亿万','亿零') set @returnStr = replace(@returnStr,'零元','元') set @returnStr = replace(@returnStr,'零零零','零') set @returnStr = replace(@returnStr,'零零','零') if (@returnStr='') begin set @returnStr = '零元' end if ((right(@returnStr,1)='元')) begin set @returnStr = @returnStr + '整' end return @returnStrEND
转载请注明原文链接：SQL Server中文大写金额转化函数
