返回顶部

收藏

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

标签:sql

收藏

0人收藏

支持

0

反对

0

相关聚客文章
  1. 博主 发表 2018-07-12 00:00:00 T-SQL游标
  2. 博主 发表 2018-07-12 00:00:00 T-SQL笔记
  3. techug 发表 2018-07-11 06:22:56 译 | 如何更优雅的写出你的SQL语句
  4. darida 发表 2018-06-19 11:24:44 Vert.x-generator 2.0.0 发布,Vert.x 代码生成器
  5. darida 发表 2018-06-19 11:24:44 Vert.x-generator 2.0.0 发布,Vert.x 代码生成器
  6. yangtingkun 发表 2018-06-04 10:09:22 SQL解决刑侦推理问题
  7. kamus 发表 2018-05-24 17:15:37 All Secondary die, Primary hang? – SQL Server 2017
  8. admin 发表 2018-05-07 09:47:38 查看SQL执行计划的方法及优劣
  9. kamus 发表 2018-05-06 07:13:01 Configuring Always On Availability Groups in SQL S
  10. 博主 发表 2012-08-24 00:00:00 [转]MySQL数据库性能优化之SQL优化
  11. jqka2 发表 2018-04-13 00:56:09 PL/SQL Developer查询中文数据乱码问号?
  12. jqka2 发表 2018-04-12 09:23:00 PL/SQL Developer配置Oracle远程连接

发表评论