sql金额转换大写.doc

sql金额转换大写CreateFunctionLowerToUpper(***@LowerMoneyDecimal(38,4))ReturnsVarchar(200)--返回的大写金额的字符******@LowerStrVarchar(50)--小写金额******@UpperStrVarchar(200)--大写金额******@UpperTmpVarchar(15)--大写金额的临时字符串******@iInt--递增量******@LowerLenInt--小写金额的总长度******@LowerStr=***@LowerMoney--把Decimal型的值全部赋给字符串变量注:(,没有的话,它会自动补0)******@LowerStr=Replace(***@LowerStr,'.','')--把小数点替换成空字符--精确到小数点的四位角分厘毫******@LowerLen=Len(***@LowerStr)--获取小写金额的总长度(包括四个小数位)******@i=1,***@UpperStr='',***@UpperTmp=''--设置默认初始值******@i<=***@******@UpperTmp=CaseWhenSubString(***@LowerStr,***@LowerLen-***@i+1,1)='0'******@i=5And(Convert(Int,Right(***@LowerStr,4))=******@LowerLen>5)Then'元'--注:如果个位为0的话,并且四位小数都是0或者它的长度超过5(也就是超过元),则为元--WhenSubString(***@LowerStr,***@LowerLen-***@i+1,1)='0'Then''Else+CaseSubString(***@LowerStr,***@LowerLen-***@i+1,1)--看当前位是数字几,就直接替换成汉字繁体大写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+******@iWhen1Then'毫'When2Then'厘'When3Then'分'When4Then'角'When5Then'元'When9Then'萬'When13Then'亿''When17Then'兆When21Then'京'When25Then'垓'When29Then'杼'When33Then'穰'When37Then'沟'--Decimal型最大长度是38后面的就不用再考虑了Else+******@i%4When2Then'拾'--拾61014182226303438…………When3Then'佰'--佰71115192327313539…………When0Then'仟'--仟81216202428323640…………******@UpperStr=Isnull(***@UpperTmp,'')+Isnull(***@UpperStr,'')******@i=***@i+1EndIfConvert(Int,Right(***@LowerStr,4))=******@Uppe