MySQL中自定义函数，然后在SQL语句中调用即可
代码语言：javascript
复制CREATE FUNCTION fun_convertm(MONEY VARCHAR(150) ) RETURNS VARCHAR(150) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE RESULT VARCHAR(100); -- 返回字符串
DECLARE NUM_ROUND VARCHAR(100); -- 转换数字为小数点后2位的字符(正数)
DECLARE NUM_LEFT VARCHAR(100); -- 小数点左边的数字
DECLARE NUM_RIGHT VARCHAR(2); -- 小数点右边的数字
DECLARE STR1 VARCHAR(10); -- 数字大写
DECLARE STR2 VARCHAR(16); -- 数字位数(从低至高)
DECLARE NUM_PRE INT; -- 前一位上的数字
DECLARE NUM_CURRENT INT; -- 当前位上的数字
DECLARE NUM_COUNT INT; -- 当前数字位数
DECLARE NUM1 INT;
SET MONEY=CONVERT(MONEY,DECIMAL(14,2));
SET NUM_ROUND=CONCAT(MONEY, '');
SET STR1='零壹贰叁肆伍陆柒捌玖';
SET STR2='圆拾佰仟万拾佰仟亿拾佰仟万拾佰仟';
SET NUM_PRE=1;
SET NUM_COUNT=0;
SET NUM_LEFT=FLOOR(MONEY);
SET NUM_RIGHT=REPLACE(NUM_ROUND,CONCAT(NUM_LEFT,'.'),'');
IF MONEY IS NULL THEN
SET RESULT=NULL;
END IF; -- 转换数字为null时返回null
IF LENGTH(NUM_LEFT)>=8 THEN SET NUM1=CAST(SUBSTR(NUM_LEFT, -8, 4) AS SIGNED);
ELSEIF LENGTH(NUM_LEFT)>4 THEN SET NUM1=CAST(SUBSTR(NUM_LEFT, -LENGTH(NUM_LEFT), LENGTH(NUM_LEFT)-4) AS SIGNED);
ELSE SET NUM1=CAST(SUBSTR(NUM_LEFT, 1, 4) AS SIGNED);
END IF;
IF LENGTH(NUM_LEFT) > 16 THEN SET RESULT='**********';
END IF; -- 数字整数部分超过16位时
-- 采用从低至高的算法，先处理小数点右边的数字
IF LENGTH(NUM_RIGHT) = 2 THEN
IF CAST(SUBSTR(NUM_RIGHT, 1, 1) AS SIGNED) = 0 THEN
SET RESULT = CONCAT('零' ,
SUBSTR(STR1, CAST(SUBSTR(NUM_RIGHT, 2, 1) AS SIGNED) 1, 1) , '分');
ELSE
SET RESULT = CONCAT(SUBSTR(STR1, CAST(SUBSTR(NUM_RIGHT, 1, 1) AS SIGNED) 1, 1) , '角' ,
SUBSTR(STR1, CAST(SUBSTR(NUM_RIGHT, 2, 1) AS SIGNED) 1, 1) , '分');
END IF;
ELSE
IF LENGTH(NUM_RIGHT) = 1 THEN
SET RESULT = CONCAT(SUBSTR(STR1, CAST(SUBSTR(NUM_RIGHT, 1, 1) AS SIGNED) 1, 1) , '角整');
ELSE
SET RESULT = '整';
END IF;
END IF;
-- 再处理小数点左边的数字
myloop:LOOP
SET NUM_COUNT=NUM_COUNT 1; -- 当前数字位数
SET NUM_CURRENT=CAST(SUBSTR(NUM_LEFT, LENGTH(NUM_LEFT)-NUM_COUNT 1, 1) AS SIGNED);
IF NUM_CURRENT > 0 THEN SET RESULT=CONCAT(SUBSTR(STR1, NUM_CURRENT 1, 1) ,
SUBSTR(STR2, NUM_COUNT, 1) , RESULT);
ELSE
IF NUM_COUNT = 5 THEN
IF MOD(NUM_COUNT - 1, 4) = 0 AND NUM1 <> 0 THEN SET RESULT = CONCAT(SUBSTR(STR2, NUM_COUNT, 1) , RESULT); SET NUM_PRE = 0;
END IF;
ELSE
IF MOD(NUM_COUNT - 1, 4) = 0 THEN SET RESULT = CONCAT(SUBSTR(STR2, NUM_COUNT, 1) , RESULT); SET NUM_PRE = 0; -- 元、万,亿前不准加零
END IF;
END IF;
IF NUM_PRE > 0 OR LENGTH(NUM_LEFT) = 1 THEN
-- 上一位数字不为0或只有个位时
SET RESULT = CONCAT(SUBSTR(STR1, NUM_CURRENT 1, 1) , RESULT);
END IF;
END IF;
SET NUM_PRE = NUM_CURRENT;
IF NUM_COUNT>=LENGTH(NUM_LEFT) THEN LEAVE myloop;
END IF;
END LOOP myloop;
IF MONEY < 0 THEN
-- 转换数字是负数时
SET RESULT =CONCAT( '负' , RESULT);
END IF;
SET RESULT=REPLACE(RESULT,'零零分','整');
SET RESULT=REPLACE(RESULT,'零分','整');
SET RESULT=REPLACE(RESULT,'元整','圆整');
RETURN RESULT;
END
