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

SQL Server 中文金额 、大写金额:
复制代码
CREATE FUNCTION fn_GetChineseCost
(
  @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
复制代码

select dbo.fn_GetChineseCost(987654321.23) as 金额 

 

posted @   每天进步多一点  阅读(253)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
历史上的今天:
2020-02-22 RESTful详解
2018-02-22 sql 2008批量删除数据表格
2017-02-22 Sql Like 通配符 模糊查询技巧及特殊字符
点击右上角即可分享
微信分享提示