SQL Server中文大写金额转化函数代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】 SQL Server中文大写金额转化函数

作者:daifu / 发布于2011/1/10/ 900

为了能够在报告中直接输入大写金额，本人编写了这个SQL Server的函数. 复制以下代码到SQL Server 查询分析器中运行，生成自定义函数 usf_ChineseCost. 在查询语句中调用该函数就可以输出大写的金额了。

<div>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,'亿')</div>
<div>set @returnStr = replace(@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,'零万','万')
set @returnStr = replace(@returnStr,'零元','元')
set @returnStr = replace(@returnStr,'零角','零')
set @returnStr = replace(@returnStr,'零分','')</div>
<div>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</div>
<div>set @returnStr = replace(@returnStr,'亿万','亿零')
set @returnStr = replace(@returnStr,'零元','元')
set @returnStr = replace(@returnStr,'零零零','零')
set @returnStr = replace(@returnStr,'零零','零')</div>
if (@returnStr='') 
begin
 set @returnStr = '零元'
end
if ((right(@returnStr,1)='元')) 
begin
 set @returnStr = @returnStr + '整'
end
return @returnStr
END</div>

试试其它关键字

　大写金额　

同语言下

可能有用的

daifu贡献的其它代码(1)

.SQL Server中文大写金额转化函数