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

最新推荐文章于 2024-12-24 09:58:29 发布

原创最新推荐文章于 2024-12-24 09:58:29 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #function #float

介绍了一个SQL Server中的函数，用于将浮点数金额转换为中文大写的表示方式，适用于财务报表等场景。

CREATE FUNCTION usf_ChineseCost 
 
 02( 
 
 03 @Cost float
 
 04) 
 
 05RETURNS varchar(50) 
 
 06AS
 
 07----大写钱数---- 
 
 08BEGIN
 
 09 declare @returnStr varchar(50) 
 
 10 if (@Cost >= 1000000000) 
 
 11    Begin
 
 12  set @returnStr = '##########'
 
 13        return @returnStr 
 
 14    end 
 
 15 set @returnStr = '00000000000'
 
 16 set @returnStr = right(@returnStr + convert(varchar,convert(decimal(11,2),round(@cost,2))),12) 
 
 17 set @returnStr = replace(@returnStr,'0','零') 
 
 18 set @returnStr = replace(@returnStr,'1','壹') 
 
 19 set @returnStr = replace(@returnStr,'2','贰') 
 
 20 set @returnStr = replace(@returnStr,'3','叁') 
 
 21 set @returnStr = replace(@returnStr,'4','肆') 
 
 22 set @returnStr = replace(@returnStr,'5','伍') 
 
 23 set @returnStr = replace(@returnStr,'6','陆') 
 
 24 set @returnStr = replace(@returnStr,'7','柒') 
 
 25 set @returnStr = replace(@returnStr,'8','捌') 
 
 26 set @returnStr = replace(@returnStr,'9','玖') 
 
 27            
 
 28 set @returnStr = @returnStr + '分'
 
 29 set @returnStr =  stuff(@returnStr,len(@returnStr)-1,0,'角') 
 
 30 set @returnStr =  replace(@returnStr,'.','元') 
 
 31 set @returnStr =  stuff(@returnStr,len(@returnStr)-5,0,'拾') 
 
 32 set @returnStr =  stuff(@returnStr,len(@returnStr)-7,0,'百') 
 
 33 set @returnStr =  stuff(@returnStr,len(@returnStr)-9,0,'千') 
 
 34 set @returnStr =  stuff(@returnStr,len(@returnStr)-11,0,'万') 
 
 35 set @returnStr =  stuff(@returnStr,len(@returnStr)-13,0,'拾') 
 
 36 set @returnStr =  stuff(@returnStr,len(@returnStr)-15,0,'百') 
 
 37 set @returnStr =  stuff(@returnStr,len(@returnStr)-17,0,'千') 
 
 38 set @returnStr =  stuff(@returnStr,len(@returnStr)-19,0,'亿') 
 
 39  
 
 40 set @returnStr =  replace(@returnStr,'零亿','零') 
 
 41 set @returnStr =  replace(@returnStr,'零千','零') 
 
 42 set @returnStr =  replace(@returnStr,'零百','零') 
 
 43 set @returnStr =  replace(@returnStr,'零拾','零') 
 
 44 set @returnStr =  replace(@returnStr,'零零零','零') 
 
 45 set @returnStr =  replace(@returnStr,'零零','零') 
 
 46 set @returnStr =  replace(@returnStr,'零万','万') 
 
 47 set @returnStr =  replace(@returnStr,'零元','元') 
 
 48 set @returnStr =  replace(@returnStr,'零角','零') 
 
 49 set @returnStr =  replace(@returnStr,'零分','') 
 
 50  
 
 51 while left(@returnStr,1) = '零'
 
 52    Begin
 
 53    set @returnStr =  right(@returnStr,len(@returnStr)-1) 
 
 54    end
 
 55 if ((left(@returnStr,1)='万'))  
 
 56 begin
 
 57  set @returnStr =  right(@returnStr,len(@returnStr)-1) 
 
 58 end
 
 59 if ((left(@returnStr,1)='元'))  
 
 60 begin
 
 61  set @returnStr =  right(@returnStr,len(@returnStr)-1) 
 
 62 end
 
 63 while left(@returnStr,1) = '零'
 
 64    Begin
 
 65    set @returnStr =  right(@returnStr,len(@returnStr)-1) 
 
 66    end
 
 67 while right(@returnStr,1) = '零'
 
 68    Begin
 
 69    set @returnStr =  left(@returnStr,len(@returnStr)-1) 
 
 70    end
 
 71  
 
 72 set @returnStr =  replace(@returnStr,'亿万','亿零') 
 
 73 set @returnStr =  replace(@returnStr,'零元','元') 
 
 74 set @returnStr =  replace(@returnStr,'零零零','零') 
 
 75 set @returnStr =  replace(@returnStr,'零零','零') 
 
 76  
 
 77 if (@returnStr='')  
 
 78 begin
 
 79  set @returnStr =  '零元'
 
 80 end
 
 81 if ((right(@returnStr,1)='元'))  
 
 82 begin
 
 83  set @returnStr =  @returnStr + '整'
 
 84 end
 
 85 return @returnStr 
 
 86END
 
转自：http://www.oschina.net/code/snippet_102716_1012