mysql 金额大写_SQLServer中文大写金额转化函数

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

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

SQL Server 中文金额 大写金额

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(@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 @returnStr

END

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网