Home  >  Article  >  Database  >  SQL Server中文大写金额转化函数

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

WBOY
WBOYOriginal
2016-06-07 14:54:341987browse

为了能够在报告中直接输入大写金额,本人编写了这个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

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn