《非常有用的sql金额数字转大写》由会员分享，可在线阅读，更多相关《非常有用的sql金额数字转大写（5页珍藏版）》请在金锄头文库上搜索。
1、非常有用的 SQL 语句-金额数字转大写发布时间：2008-02-27 21:25 作者：江健锦在 SQL Server 中使用的函数！一、小写金额转大写金额REATE FUNCTION CapitalRMB(ChangeMoney numeric(15,2) RETURNS VarChar(100) AS BEGIN Declare String1 char(20)Declare String2 char(30)Declare String4 Varchar(100)Declare String3 Varchar(100) -从原 A 值中取出的值Declare i int -循环变量Dec
2、lare J Int -A 的值乘以 100 的字符串长度Declare Ch1 Varchar(100) -数字的汉语读法Declare Ch2 Varchar(100) -数字位的汉字读法Declare Zero Int -用来计算连续有几个零Declare ReturnValue VarChar(100)Select ReturnValue = Select String1 = 零壹贰叁肆伍陆柒捌玖 Select String2 = 万仟佰拾亿仟佰拾万仟佰拾元角分Select String4 = Cast(ChangeMoney*100 as int) select J=len(cast
3、(ChangeMoney*100) as int)Select String2=Right(String2,J)Select i = 1 while i0 BeginSelect Ch1 = Substring(String1, Cast(String3 as Int) + 1, 1)Select Ch2 = Substring(String2, i, 1)Select Zero = 0 -表示本位不为零endelse BeginIf (Zero = 0) Or (i = J - 9) Or (i = J - 5) Or (i = J - 1)Select Ch1 = 零 ElseSelect
4、 Ch1 = Select Zero = Zero + 1 -表示本位为 0-如果转换的数值需要扩大，那么需改动以下表达式 I 的值。Select Ch2 = If i = J - 10 BeginSelect Ch2 = 亿Select Zero = 0 endIf i = J - 6 BeginSelect Ch2 = 万Select Zero = 0endif i = J - 2 BeginSelect Ch2 = 元Select Zero = 0endIf i = J Select Ch2 = 整end Select ReturnValue = ReturnValue + Ch1 +
5、Ch2select i = i+1end-最后将多余的零去掉If CharIndex(仟仟,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 仟仟, 仟)If CharIndex(佰佰,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 佰佰, 佰)If CharIndex(零元,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 零元, 元)If CharIndex(零万,ReturnValue) 0 Se
6、lect ReturnValue = Replace(ReturnValue, 零万, 万)If CharIndex(零亿,ReturnValue) 0Select ReturnValue = Replace(ReturnValue, 零亿, 亿)If CharIndex(零整,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 零整, 整)If CharIndex(零佰,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 零佰, 零)If CharIndex(零仟,Re
7、turnValue) 0 Select ReturnValue = Replace(ReturnValue, 零仟, 零)If CharIndex(元元,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 元元, 元)return ReturnValueEND二、小写数字转大写数字CREATE FUNCTION CapitalNUM(ChangeMoney numeric(15,2) RETURNS VarChar(100) AS BEGIN Declare String1 char(20)Declare String2 char(
8、30)Declare String4 Varchar(100)Declare String3 Varchar(100) -从原 A 值中取出的值Declare i int -循环变量Declare J Int -A 的值乘以 100 的字符串长度Declare Ch1 Varchar(100) -数字的汉语读法Declare Ch2 Varchar(100) -数字位的汉字读法Declare Zero Int -用来计算连续有几个零Declare ReturnValue VarChar(100)Select ReturnValue = Select String1 = 零壹贰叁肆伍陆柒捌玖 S
9、elect String2 = 万仟佰拾亿仟佰拾万仟佰拾点零零Select String4 = Cast(ChangeMoney*100 as int) select J=len(cast(ChangeMoney*100) as int)Select String2=Right(String2,J)Select i = 1 while i0 BeginSelect Ch1 = Substring(String1, Cast(String3 as Int) + 1, 1)Select Ch2 = Substring(String2, i, 1)Select Zero = 0 -表示本位不为零en
10、delse 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 - 10 BeginSelect Ch2 = 亿Select Zero = 0endIf i = J - 6 BeginSelect Ch2 = 万 Select Zero = 0endif i = J - 2 BeginSelect
11、 Ch2 = 点Select Zero = 0endIf i = J Select Ch2 = 整end if (i = J) or ( i = J-1) beginSelect Ch2 = endSelect ReturnValue = ReturnValue + Ch1 + Ch2select i = i+1end-最后将多余的零去掉If CharIndex(仟仟,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 仟仟, 仟)If CharIndex(佰佰,ReturnValue) 0 Select ReturnValue =
12、 Replace(ReturnValue, 佰佰, 佰)If CharIndex(零元,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 零元, 元)If CharIndex(零万,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 零万, 万)If CharIndex(零亿,ReturnValue) 0Select ReturnValue = Replace(ReturnValue, 零亿, 亿)If CharIndex(零整,ReturnValue) 0 Selec
13、t ReturnValue = Replace(ReturnValue, 零整, 整)If CharIndex(零佰,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 零佰, 零)If CharIndex(零仟,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 零仟, 零)If CharIndex(零点零,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 零点零, )If CharIndex(零点,ReturnValue) 0 Select ReturnValue = Replace(ReturnValue, 零点, 点)return ReturnValueEND三、小写数字直接改大写数字，不加十百千万，一对一转create FUNCTION CapitalDig(ChangeMoney numeric(15,2) RETURNS VarChar(100) AS BEGIN Declare String1 char(20)Declare String4 Varchar(100)Declare String3 Varchar(100) -从原 A 值中取出的值Declare i
