2022年金额阿拉伯数字转换为中文的自定义函数SQL server]教程.docx

《2022年金额阿拉伯数字转换为中文的自定义函数SQL server]教程.docx》由会员分享，可在线阅读，更多相关《2022年金额阿拉伯数字转换为中文的自定义函数SQL server]教程.docx（5页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、2022年金额阿拉伯数字转换为中文的自定义函数SQL server教程CREATE FUNCTION ChangeBigSmall(ChangeMoney money)RETURNS VarChar(100) ASBEGINDeclareString1char(20)DeclareString2char(30)DeclareString4Varchar(100)Declare String3Varchar(100)-从原A值中取出的值Declare iint-循环变量Declare JInt-A的值乘以100的字符串长度Declare Ch1Varchar(100)-数字的汉语读法Declar

2、e Ch2Varchar(100)-数字位的汉字读法Declare ZeroInt-用来计算连续有几个零DeclareReturnValueVarChar(100)SelectReturnValue = SelectString1 = 零壹贰叁肆伍陆柒捌玖SelectString2 = 万仟佰拾亿仟佰拾万仟佰拾元角分Select String4 = Cast(ChangeMoney*100 as int)select J=len(cast(ChangeMoney*100) as int)Select String2=Right(String2,J)Selecti = 1whilei<=

3、j BeginSelect String3 = Substring(String4,i,1)if String3<>0 BeginSelectCh1 = Substring(String1, Cast(String3 as Int) + 1, 1)SelectCh2 = Substring(String2, i, 1)SelectZero = 0-表示本位不为零endelse BeginIf (Zero = 0) Or (i = J - 9) Or (i = J - 5) Or (i = J - 1)Select Ch1 = 零ElseSelect Ch1 = Select Zer

4、o = Zero + 1-表示本位为0-假如转换的数值须要扩大，那么需改动以下表达式 I 的值。Select Ch2 = If i = J - 10BeginSelect Ch2 = 亿Select Zero = 0endIf i = J - 6 BeginSelect Ch2 = 万Select Zero = 0endif i = J - 2 BeginSelect Ch2 = 元Select Zero = 0endIf i = JSelect Ch2 = 整endSelect ReturnValue = ReturnValue + Ch1 + Ch2select i = i+1end-最终

5、将多余的零去掉If CharIndex(仟仟,ReturnValue) <> 0Select ReturnValue = Replace(ReturnValue, 仟仟, 仟)If CharIndex(佰佰,ReturnValue) <> 0Select ReturnValue = Replace(ReturnValue, 佰佰, 佰)If CharIndex(零元,ReturnValue) <> 0Select ReturnValue = Replace(ReturnValue, 零元, 元)If CharIndex(零万,ReturnValue) <> 0Select ReturnValue = Replace(ReturnValue, 零万, 万)If CharIndex(零亿,ReturnValue) <> 0Select ReturnValue = Replace(ReturnValue,