집 >데이터 베이스 >MySQL 튜토리얼 >SQL Server中文大写金额转化函数
为了能够在报告中直接输入大写金额,本人编写了这个SQL Server的函数. 复制以下代码到SQL Server 查询分析器中运行,生成自定义函数 usf_ChineseCost. 在查询语句中调用该函数就可以输出大写的金额了。 SQL Server 中文金额 大写金额 CREATE FUNCTION usf_Ch
为了能够在报告中直接输入大写金额,本人编写了这个SQL Server的函数. 复制以下代码到SQL Server 查询分析器中运行,生成自定义函数 usf_ChineseCost. 在查询语句中调用该函数就可以输出大写的金额了。
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