SQL函数_小写金额转换成大写
informix小写金额转大写函数
informix小写金额转大写函数在Informix中，可以使用以下函数将小写金额转换为大写形式：1.创建一个存储过程：```sqlCREATE PROCEDURE convert_amount_to_words (in_amount DECIMAL(10,2), out_words VARCHAR(500))RETURNINGVARCHAR(500);DEFINE units VARCHAR(10);DEFINE tens VARCHAR(10);DEFINE hundreds VARCHAR(10);DEFINE thousands VARCHAR(10);DEFINE words VARCHAR(500);DEFINE temp_amount DECIMAL(10,2);DEFINE integer_amount DECIMAL(10);DEFINE decimal_amount DECIMAL(2);DEFINE temp_words VARCHAR(100);LET units = 'ZERO', 'ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX', 'SEVEN', 'EIGHT', 'NINE';LET tens = '', '', 'TWENTY', 'THIRTY', 'FORTY', 'FIFTY','SIXTY', 'SEVENTY', 'EIGHTY', 'NINETY';LET hundreds = '', 'ONE HUNDRED', 'TWO HUNDRED', 'THREE HUNDRED', 'FOUR HUNDRED','FIVEHUNDRED','SIXHUNDRED','SEVENHUNDRED','EIGHTHUNDRED','NI NEHUNDRED';LET thousands = '', 'ONE THOUSAND', 'TWO THOUSAND', 'THREE THOUSAND', 'FOUR THOUSAND','FIVETHOUSAND','SIXTHOUSAND','SEVENTHOUSAND','EIGHTTHOUSAND' ,'NINETHOUSAND';LET temp_amount = TRUNC(in_amount, 2);LET integer_amount = TRUNC(temp_amount);LET decimal_amount = (temp_amount - integer_amount) * 100;IF integer_amount = 0 THENLET words = units[0] ， ' DOLLARS AND ' ，LTRIM(decimal_amount) ， '/100';ELSEIF integer_amount / 1000 > 0 THENLET temp_words = convert_amount_to_words(integer_amount / 1000, '');LET words = temp_words ， ' THOUSAND';LET integer_amount = integer_amount MOD 1000;ENDIF;IF integer_amount / 100 > 0 THENLET words = words ， ' ' ， hundreds[TRUNC(integer_amount / 100)];LET integer_amount = integer_amount MOD 100;ENDIF;IF integer_amount / 10 > 1 THENLET words = words ， ' ' ， tens[TRUNC(integer_amount / 10)];LET integer_amount = integer_amount MOD 10;ENDIF;IF integer_amount > 0 THENLET words = words ， ' ' ， units[integer_amount];ENDIF;LET words = words ， ' DOLLARS AND ' ，LTRIM(decimal_amount) ， '/100';ENDIF;LET out_words = words;RETURN out_words;ENDPROCEDURE;```2.调用存储过程：```sqlLET amount = 1200.45;LET words = '';CALL convert_amount_to_words(amount, words);SELECT words;```在上述代码中，存储过程`convert_amount_to_words`接收一个小数类型的金额参数`in_amount`和一个字符类型的输出参数`out_words`，返回一个字符类型的金额大写形式。
sql server函数将人民币数字转换成大写形式
1、sql 数据库中的存储过程的参数问题怎么将sql数据库中的存储过程中的参数既作为输出变量又作为输出变量? [sql] view plaincopy --drop proc proc_test--gocreate proc dbo.proc_test@in int,@out int out,@in_out int outputasselect @out = @in + @in_out, --1 + 2 = 3@in_out = @out + 1 --3 + 1 = 4godeclare @in_p intdeclare @out_p intdeclare @in_out_p intset @in_p = 1;set @in_out_p = 2exec dbo.proc_test @in_p,@out_p out,@in_out_p outputselect @in_p, --输入参数@out_p, --输出参数@in_out_p --输入，输出参数/*(无列名) (无列名) (无列名)1 3 4*/2、在存储过程中的参数问题。
下面是问题：[sql] view plaincopy create table #tabletest(id int identity , name varchar(20)，age int,) goinsert into #tabletestselect '小明',23 union allselect '小红',28 union allselect '小军',27goselect *from #tabletestgocreate proc proctest@name varchar(20)，@age int,@ids varchar(30)asbeginselect *from #tabletest where 1=1end--当我传入@name参数等于小明，23岁，还有id在(1,3)的时候--我怎么可以弄成可选的参数--比如，name不为空时候select *from #tabletest where 1=1 and name like '小明'--如果name参数为空的时候，ids参数不为空的时候select *from #tabletest where 1=1 and id in(1,3)--请问一下，就有参数不为空的时候存储过程中的sql追加条件，为空的时候就不追加，这样带可选参数的存储过程怎么写，以及怎么调用，请帮小弟写一个实例这种问题，本质上就是根据传入的参数不同，进行不同的查询，也就是where 后面的查询条件是动态的。
小写金额转换大写金额函数公式
小写金额转换大写金额函数公式1.概述在日常生活和工作中，经常会与人进行金融交易，其中包括付款和收款。
在执行这些交易时，我们需要将商品或服务的价格转换为文字，称为大写金额。
大写金额与阿拉伯数字金额相比更加容易理解和记录，也可以提高交易的清晰度，因此在许多场合非常必要。
本文将介绍一个通用的小写金额转换大写金额的函数公式。
2.函数公式以下是一种通用的小写金额转换大写金额的函数公式：def digit_uppercase(digit):if digit>999999999999.99or digit<0:return"输入金额过大或者过小，请重新输入！"digits,fractions=str(round(digit,2)).split('.')digits=digits[::-1]result=[]if len(digits)>0:result.append('圆')zero_flag=Falsefor index in range(len(digits)):if digits[index]!='0':zero_flag=Falseresult.append('壹贰叁肆伍陆柒捌玖'[int(digits[index])])if index==0:result.append('亿')elif index==1:result.append('拾')elif index==2:result.append('佰')elif index==3:result.append('仟')elif index==4:result.append('万')elif index==5:result.append('拾')elif index==6:result.append('佰')elif index==7:result.append('仟')else:if not zero_flag:zero_flag=Trueresult.append('零')if index==4or index==8:result.append('万')if len(digits)==1or digits[1]=='0': result.append('元整')if len(digits)>1and digits[1]!='0': result.append('元')if len(digits)>1:for index in range(1,len(digits)): if index==1:result.append('角')elif index==2:result.append('分')if digits[index]!='0':result.append('壹贰叁肆伍陆柒捌玖'[int(digits[index])])else:result.append('零元整')return''.join(result[::-1])3.函数公式详解这个函数的输入参数是小写金额digit，小数点后最多只能有两位数字。
金额小写转大写 sql函数 & excel-vba fun
金额小写转大写sql函数1.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO--功能：用于将小写的数值翻译成大写的字符串（支持到分，即小数点后两位）--入口参数：@decNum------数字型变量--返回：字符串--举例：select dbo.fn_ChnMoney(623.88)-- 结果为“陆佰贰拾叁元捌角捌分”ALTER FUNCTION [dbo].[fn_ChnMoney_New](@decNum decimal(18,2)) RETURNS varchar(200)ASBEGINDECLARE @chvNum varchar(200)DECLARE @chvMoney varchar(200)DECLARE @chvTemp varchar(200)DECLARE @intIntLen intDECLARE @intI intDECLARE @chvTempI varchar(200)DECLARE @chvReturn varchar(200)DECLARE @sFsFlag intSET @sFsFlag = 0IF @decNum=0SET @chvMoney ='零'ElseBEGINIF @decNum<0BEGINSET @sFsFlag = 1SET @decNum =ABS(@decNum)ENDSET @chvTemp=convert(varchar(200),Round(@decNum*100,0))IF charindex('.',@chvTemp,1)>0SET @chvNum=left(@chvTemp,charindex('.',@chvTemp,1)-1) ELSESET @chvNum=@chvTempSET @intIntLen=len(@chvNum)SET @chvMoney=''Set @chvReturn =''SET @intI=1WHILE @intI <= @intIntLenBEGINSET @chvTempI =substring(@chvNum,@intIntLen-@intI+1,1)SET @chvMoney =substring('零壹贰叁肆伍陆柒捌玖',convert(int,@chvTempI)+1,1)+substring('分角元拾佰仟万拾佰仟亿拾佰仟兆拾佰仟京拾佰仟',(@intI-1)+1,1)SET @intI = @intI + 1SET @chvReturn = @chvMoney + @chvReturnENDENDSET @chvReturn=Replace(@chvReturn,'零仟','零')SET @chvReturn=Replace(@chvReturn,'零佰','零')SET @chvReturn=Replace(@chvReturn,'零拾','零')while charindex('零零',@chvReturn,1)>0SET @chvReturn=Replace(@chvReturn,'零零','零')SET @chvReturn=Replace(@chvReturn,'零兆','兆')SET @chvReturn=Replace(@chvReturn,'零亿','亿')SET @chvReturn=Replace(@chvReturn,'零万','万')SET @chvReturn=Replace(@chvReturn,'零元','元')SET @chvReturn=Replace(@chvReturn,'零角零分','整')SET @chvReturn=Replace(@chvReturn,'零角','零')SET @chvReturn=Replace(@chvReturn,'零分','整')SET @chvReturn=LTRIM(RTRIM(@chvReturn))IF @sFsFlag = 1SET @chvReturn ='负'+Ltrim(@chvReturn) RETURN @chvReturnEND2.数字转换（不带元角分）set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO--功能：用于将小写的数值翻译成大写的字符串（支持到分，即小数点后两位）--入口参数：@decNum------数字型变量--返回：字符串--举例：select dbo.fn_ChnMoney(623.88)-- 结果为“陆佰贰拾叁元捌角捌分”ALTER FUNCTION [dbo].[fn_ChnMoney](@decNum decimal(18,2)) RETURNS varchar(200)ASBEGINDECLARE @chvNum varchar(200)DECLARE @chvMoney varchar(200)DECLARE @chvTemp varchar(200)DECLARE @intIntLen intDECLARE @intI intDECLARE @chvTempI varchar(200)DECLARE @chvReturn varchar(200)IF @decNum=0SET @chvMoney ='零'ElseBEGINSET @chvTemp=convert(varchar(200),Round(@decNum*100,0))IF charindex('.',@chvTemp,1)>0SET @chvNum=left(@chvTemp,charindex('.',@chvTemp,1)-1) ELSESET @chvNum=@chvTempSET @intIntLen=len(@chvNum)SET @chvMoney=''Set @chvReturn =''SET @intI=1WHILE @intI <= @intIntLenBEGINSET @chvTempI =substring(@chvNum,@intIntLen-@intI+1,1)SET @chvMoney =substring('零壹贰叁肆伍陆柒捌玖',convert(int,@chvTempI)+1,1)+substring('分角元拾佰仟万拾佰仟亿拾佰仟兆拾佰仟京拾佰仟',(@intI-1)+1,1)SET @intI = @intI + 1SET @chvReturn = @chvMoney + @chvReturnENDENDSET @chvReturn=Replace(@chvReturn,'零仟','零')SET @chvReturn=Replace(@chvReturn,'零佰','零')SET @chvReturn=Replace(@chvReturn,'零拾','零')while charindex('零零',@chvReturn,1)>0SET @chvReturn=Replace(@chvReturn,'零零','零')SET @chvReturn=Replace(@chvReturn,'零兆','兆')SET @chvReturn=Replace(@chvReturn,'零亿','亿')SET @chvReturn=Replace(@chvReturn,'零万','万')SET @chvReturn=Replace(@chvReturn,'零元','元')SET @chvReturn=Replace(@chvReturn,'零角零分','整')SET @chvReturn=Replace(@chvReturn,'零角','零')SET @chvReturn=Replace(@chvReturn,'零分','整')SET @chvReturn=LTRIM(RTRIM(@chvReturn))RETURN @chvReturnENDExcel vba 函数，数字转化成大写人民币Function DXRMB(M)'函数名为DXRMBy = Int(Round(100 * Abs(M)) / 100)j = Round(100 * Abs(M) + 0.00001) - y * 100f = (j / 10 - Int(j / 10)) * 10a = IIf(y < 1, "", Application.Text(y, "[DBNum2]") & "元")b = IIf(j > 9.5, Application.Text(Int(j / 10), "[DBNum2]") & "角", IIf(y < 1, "", IIf(f > 1, "零", "")))c = IIf(f < 1, "整", Application.Text(Round(f, 0), "[DBNum2]") & "分")DXRMB = IIf(Abs(M) < 0.005, "", IIf(M < 0, "负" & a & b & c, a & b & c))End Function。
小写转换成大写
when 9 then '拾'
when 10 then '佰'
when 11 then '仟'
when 12 then '亿'
when 13 then '拾'
when 14 then '佰'
when 15 then '仟'
when 16 then '万'
select @v_upperstr=replace(@v_upperstr,'零零','零')
end
-----对壹元以下的金额的处理
if(substring(@v_upperstr,1,1)='元')
begin
select @v_upperstr=substring(@v_upperstr,2,(len(v_upperstr)-1))
declare @v_upperpart varchar(200)
declare @v_upperstr varchar(200)---大写金额
declare @i_l int
declare @ret varchar(200)
select @v_lowerstr = ltrim(rtrim(str(@n_moneny,20,2)))---四舍五入为指定的精度，并删除数据左右的空格
select @i_l=1
select @v_upperstr=''
while (@i_l<=len(@v_lowerstr))//循环条件
SQLServer中文大写金额转化函数
SQLServer中文大写金额转化函数SQL Server中文大写金额转化函数CREATE FUNCTION usf_ChineseCost(@Cost float)RETURNS varchar(50)AS----大写钱数----BEGINdeclare @returnStr varchar(50)if (@Cost >= 1000000000)Beginset @returnStr = '##########'return @returnStrendset @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) = '零'Beginset @returnStr = right(@returnStr,len(@returnStr)-1)endif ((left(@returnStr,1)='万'))beginset @returnStr = right(@returnStr,len(@returnStr)-1)endif ((left(@returnStr,1)='元'))beginset @returnStr = right(@returnStr,len(@returnStr)-1)endwhile left(@returnStr,1) = '零'Beginset @returnStr = right(@returnStr,len(@returnStr)-1)endwhile right(@returnStr,1) = '零'Beginset @returnStr = left(@returnStr,len(@returnStr)-1)endset @returnStr = replace(@returnStr,'亿万','亿零')set @returnStr = replace(@returnStr,'零元','元') set @returnStr = replace(@returnStr,'零零零','零')set @returnStr = replace(@returnStr,'零零','零')if (@returnStr='')beginset @returnStr = '零元'endif ((right(@returnStr,1)='元'))beginset @returnStr = @returnStr + '整' endreturn @returnStrEND。
SQL小写金额转换成大写
set @v_UpperStr = REPLACE(@v_UpperStr,'零万零元','万元')
set @v_UpperStr = REPLACE(@v_UpperStr,'万零元','万元')
WHEN '3' THEN '叁'
WHEN '4' THEN '肆'
WHEN '5' THEN '伍'
WHEN '6' THEN '陆'
WHEN '7' THEN '柒'
WHEN '8' THEN '捌'
WHEN '9' THEN '玖'
ENDN '分'
end
if ( 0 = @v_TransType)
begin
set @v_UpperStr = REPLACE(@v_UpperStr,'零拾','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零佰','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零仟','零')
begin
select @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1)
支持负数的金额转大写的sql函数
/*金额阿拉伯数字转换为中文的自定义函数参数一：金额参数二：0到元补整；1到角补整*/DROP FUNCTION [dbo].[FAtoC]GOCREATE function FAtoC (@amount decimal(18, 2), @type int) returns nvarchar(36) as begindeclare @digital nvarchar(10) = '零壹贰叁肆伍陆柒捌玖'declare @position nvarchar(18) = '万仟佰拾亿仟佰拾万仟佰拾元角分'declare @inputstr varchar(18)declare @l int --金额乘以100后的字符串长度declare @i int --循环变量declare @zero int --连续零计数器declare @posvalue int --从金额中取出一位的值declare @chdig nvarchar(1) --数字大写declare @chpos nvarchar(1) --数字位大写declare @returnvalue nvarchar(36) = ''if abs(@amount) < 1set @inputstr = cast((abs(@amount) * 100) as int)elseset @inputstr = replace(abs(@amount), '.', '')set @l = len(@inputstr)set @position = right(@position, @l)set @i = 1while @i <= @lbeginset @posvalue = cast(substring(@inputstr, @i, 1) as int) --取x位数字set @chdig = substring(@digital, @posvalue + 1, 1) --x位数字转大写set @chpos = substring(@position, @i, 1) --x位对应位大写if @posvalue > 0begin--补回除亿、万、元位外的零if (@zero > 0) and ((@l - @i + 3) % 4 != 0)set @returnvalue = @returnvalue + '零'set @zero = 0endif @posvalue = 0beginset @chdig = ''--去零对应位大写，保留亿、万、元位if ((@l - @i + 2) % 4 != 0)set @chpos = ''--补整（到角补整第二参数输入1）if (@zero + @type > 0) and (@i = @l)set @chpos = '整'set @zero = @zero + 1endset @returnvalue = @returnvalue + @chdig + @chposset @i = @i + 1endif @amount < 0set @returnvalue = '负' + @returnvalueif @amount = 0set @returnvalue = '零元'set @returnvalue = replace(@returnvalue, '亿万', '亿')return(@returnvalue)endGO。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
SQL函数_小写金额转换成大写/********************************************************作者：版本：1.0创建时间：20020227修改时间：功能：小写金额转换成大写参数：n_LowerMoney 小写金额v_TransType 种类 -- 1: directly translate, 0: read it in words输出：大写金额********************************************************/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[L2U]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[L2U]GOCREATE FUNCTION dbo.L2U(@n_LowerMoney numeric(15,2),@v_TransType int)RETURNS VARCHAR(200) ASBEGINDeclare @v_LowerStr VARCHAR(200) -- 小写金额Declare @v_UpperPart VARCHAR(200)Declare @v_UpperStr VARCHAR(200) -- 大写金额Declare @i_I intset @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,2))) --四舍五入为指定的精度并删除数据左右空格set @i_I = 1set @v_UpperStr = ''while ( @i_I <= len(@v_LowerStr))beginselect @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1)WHEN '.' THEN '元'WHEN '0' THEN '零'WHEN '1' THEN '壹'WHEN '2' THEN '贰'WHEN '3' THEN '叁'WHEN '4' THEN '肆'WHEN '5' THEN '伍'WHEN '6' THEN '陆'WHEN '7' THEN '柒'WHEN '8' THEN '捌'WHEN '9' THEN '玖'END+case @i_IWHEN 1 THEN '分'WHEN 2 THEN '角'WHEN 3 THEN ''WHEN 4 THEN ''WHEN 5 THEN '拾'WHEN 6 THEN '佰'WHEN 7 THEN '仟'WHEN 8 THEN '万'WHEN 9 THEN '拾'WHEN 10 THEN '佰'WHEN 11 THEN '仟'WHEN 12 THEN '亿'WHEN 13 THEN '拾'WHEN 14 THEN '佰'WHEN 15 THEN '仟'WHEN 16 THEN '万'ELSE ''ENDset @v_UpperStr = @v_UpperPart + @v_UpperStrset @i_I = @i_I + 1endif ( 0 = @v_TransType)beginset @v_UpperStr = REPLACE(@v_UpperStr,'零拾','零')set @v_UpperStr = REPLACE(@v_UpperStr,'零佰','零')set @v_UpperStr = REPLACE(@v_UpperStr,'零仟','零')set @v_UpperStr = REPLACE(@v_UpperStr,'零零零','零')set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')set @v_UpperStr = REPLACE(@v_UpperStr,'零角零分','整')set @v_UpperStr = REPLACE(@v_UpperStr,'零分','整')set @v_UpperStr = REPLACE(@v_UpperStr,'零角','零')set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万零元','亿元')set @v_UpperStr = REPLACE(@v_UpperStr,'亿零万零元','亿元')set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万','亿')set @v_UpperStr = REPLACE(@v_UpperStr,'零万零元','万元')set @v_UpperStr = REPLACE(@v_UpperStr,'万零元','万元')set @v_UpperStr = REPLACE(@v_UpperStr,'零亿','亿')set @v_UpperStr = REPLACE(@v_UpperStr,'零万','万')set @v_UpperStr = REPLACE(@v_UpperStr,'零元','元')set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')end-- 对壹元以下的金额的处理if ( '元' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif ( '零' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))endif ( '角' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif ( '分' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif ('整' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = '零元整'endreturn @v_UpperStrENDGOselect dbo.L2U(12.93,1),dbo.L2U(12.93,0)CREATE PROCEDURE dbo.L2U(@n_LowerMoney numeric(15,2),@v_TransType int,@RET VARCHAR(200) output)ASDeclare @v_LowerStr VARCHAR(200) -- 小写金额Declare @v_UpperPart VARCHAR(200)Declare @v_UpperStr VARCHAR(200) -- 大写金额Declare @i_I intset nocount Onselect @v_LowerStr = LTRIM(RTRIM(STR(@n_LowerMoney,20,2))) --四舍五入为指定的精度并删除数据左右空格select @i_I = 1select @v_UpperStr = ''''while ( @i_I <= len(@v_LowerStr))beginselect @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1) WHEN '.' THEN '元'WHEN '0' THEN '零'WHEN '1' THEN '壹'WHEN '2' THEN '贰'WHEN '3' THEN '叁'WHEN '4' THEN '肆'WHEN '5' THEN '伍'WHEN '6' THEN '陆'WHEN '7' THEN '柒'WHEN '8' THEN '捌'WHEN '9' THEN '玖'END+case @i_IWHEN 1 THEN '分'WHEN 2 THEN '角'WHEN 3 THEN ''WHEN 4 THEN ''WHEN 5 THEN '拾'WHEN 6 THEN '佰'WHEN 7 THEN '仟'WHEN 8 THEN '万'WHEN 9 THEN '拾'WHEN 10 THEN '佰'WHEN 11 THEN '仟'WHEN 12 THEN '亿'WHEN 13 THEN '拾'WHEN 14 THEN '佰'WHEN 15 THEN '仟'WHEN 16 THEN '万'ELSE''''ENDselect @v_UpperStr = @v_UpperPart + @v_UpperStrselect @i_I = @i_I + 1end--------print '//v_UpperStr ='+@v_UpperStr +'//'if ( @v_TransType=0 )beginselect @v_UpperStr = REPLACE(@v_UpperStr,'零拾','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零佰','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零仟','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零零零','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零角零分','整')select @v_UpperStr = REPLACE(@v_UpperStr,'零分','整')select @v_UpperStr = REPLACE(@v_UpperStr,'零角','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万零元','亿元') select @v_UpperStr = REPLACE(@v_UpperStr,'亿零万零元','亿元')select @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万','亿')select @v_UpperStr = REPLACE(@v_UpperStr,'零万零元','万元')select @v_UpperStr = REPLACE(@v_UpperStr,'万零元','万元')select @v_UpperStr = REPLACE(@v_UpperStr,'零亿','亿')select @v_UpperStr = REPLACE(@v_UpperStr,'零万','万')select @v_UpperStr = REPLACE(@v_UpperStr,'零元','元')select @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')end-- 对壹元以下的金额的处理if ( substring(@v_UpperStr,1,1)='元' )beginselect @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif (substring(@v_UpperStr,1,1)= '零')beginselect @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif (substring(@v_UpperStr,1,1)='角')beginselect @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif ( substring(@v_UpperStr,1,1)='分')beginselect @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))endif (substring(@v_UpperStr,1,1)='整')beginselect @v_UpperStr = '零元整'endselect @ret=@v_UpperStrGO--调用过程：declare @ret varchar(200)exec L2U 567983.897,1,@ret outputselect @retCREATE FUNCTION [dbo].[f_num_chn] (@num numeric(14,2))RETURNS varchar(100) WITH ENCRYPTIONASBEGIN--版权所有：pbsqlDECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i intSET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)),14) SET @c_data=''SET @i=1WHILE @i<=14BEGINSET @n_str=SUBSTRING(@n_data,@i,1)IF @n_str<>' 'BEGINIF not ((SUBSTRING(@n_data,@i,2)='00') or((@n_str='0') and ((@i=4) or (@i=8) or (@i=12) or (@i=14))))SET @c_data=@c_data+SUBSTRING('零壹贰叁肆伍陆柒捌玖',CAST(@n_str AS int)+1,1) IF not ((@n_str='0') and (@i<>4) and (@i<>8) and (@i<>12))SET @c_data=@c_data+SUBSTRING('仟佰拾亿仟佰拾万仟佰拾圆角分',@i,1)IF SUBSTRING(@c_data,LEN(@c_data)-1,2)='亿万'SET @c_data=SUBSTRING(@c_data,1,LEN(@c_data)-1)ENDSET @i=@i+1ENDIF @num<0SET @c_data='（负数）'+@c_dataIF @num=0SET @c_data='零圆'IF @n_str='0'SET @c_data=@c_data+'整'RETURN(@c_data)ENDGO。