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

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)-数字的汉语读法Declare 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<= 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 Zero = 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-最终将多余的零去掉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, '