第一版:
样例:
SELECT NewProc(998435)
nine hundred and ninety eight thousand four hundred and thirty five
CREATE DEFINER=`root`@`%` FUNCTION `NewProc`(MONEY DECIMAL( 15, 2 )) RETURNS varchar(400) CHARSET utf8
BEGIN
DECLARE result VARCHAR(400); -- 返回字符串
DECLARE i INT DEFAULT 0;
DECLARE hundreds INT;
DECLARE tenth INT;
DECLARE one INT;
DECLARE thousand INT;
DECLARE million INT;
DECLARE billion INT;
DECLARE NUMBERS VARCHAR(400);
DECLARE s VARCHAR( 15 );
-- set i=0;
Set RESULT='';
Set NUMBERS='one two three four five six seven eight nine ten eleven tweleve thirteen fourteen fifteen sixteen seventeen eighteen nineteen twenty thirty forty fifty sixty seventy eighty ninety ';
Set s=LPAD(MONEY,15,'0');
Set billion=Substring(s,1,3);
Set million=Substring(s,4,3);
Set thousand=Substring(s,7,3);
IF MONEY= 0 THEN
SET RESULT =CONCAT('zero');
ELSE
WHILE i<=3 DO
Set hundreds=Substring(s,i*3+1,1);
Set tenth=Substring(s,i*3+2,1);
Set one=CONCAT((Case tenth When 1 Then 10 Else 0 End),Substring(s,i*3+3,1));
Set tenth=(Case When tenth<=1 Then 0 Else tenth End);
IF (i=3 and (billion>0 or million>0 or thousand>0) and (hundreds=0 and (tenth>0 or one>0))) THEN
Set result=CONCAT(trim(result),' and ');
END IF;
IF hundreds>0 THEN
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,hundreds*10-9,10)),' hundred ');
END IF;
IF tenth>=2 and tenth<=9 THEN
IF hundreds>0 THEN
Set result=CONCAT(trim(result),' and ');
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,tenth*10+171,10)),' ');
END IF;
END IF;
IF one>=1 and one<=19 THEN
IF hundreds>0 and tenth=0 THEN
Set result=CONCAT(trim(result),' and ');
-- Set result=CONCAT(result,TRIM(Substring(numbers,one*10-9,10)));
END IF;
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,one*10-9,10)));
END IF;
IF i=0 and billion>0 THEN
Set result=CONCAT(trim(result),' billion ');
END IF;
IF i=1 and million>0 THEN
Set result=CONCAT(trim(result),' million ');
END IF;
IF i=2 and thousand>0 THEN
Set result=CONCAT(trim(result),' thousand ');
END IF;
set i=i+1;
END WHILE;
IF(result<>'') THEN
Set result=CONCAT(trim(result),' ');
END IF;
IF Substring(s,14,2)<>'00' THEN
Set tenth=Substring(s,14,1);
Set one=Substring(s,15,1);
IF(tenth>=2 and tenth<=9) THEN
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,tenth*10+171,10)));
END IF;
IF tenth=1 and one>=1 and one<=19 THEN
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,Substring(s,14,2)*10-9,10)));
ELSE
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,one*10-9,10)));
END IF;
END IF;
END IF;
RETURN RESULT;
END
参考SQL Server SQL货币数字转英文字符语句
目前的方法比较笨,之后会出其他版本到博客,再看吧
这个版本的逻辑有些问题,hundred>0的情况包括了,却没有=0的情况,顺带兼顾了英文语法ninty-nine,所以今天做了逻辑上的优化。接下来要写小数点了(需要大换新)。
CREATE DEFINER=`root`@`%` FUNCTION `NewProc`(MONEY DECIMAL( 15, 2 )) RETURNS varchar(400) CHARSET utf8
BEGIN
DECLARE result VARCHAR(400); -- 返回字符串
DECLARE i INT DEFAULT 0;
DECLARE hundreds INT;
DECLARE tenth INT;
DECLARE one INT;
DECLARE thousand INT;
DECLARE million INT;
DECLARE billion INT;
DECLARE NUMBERS VARCHAR(400);
DECLARE s VARCHAR( 15 );
-- set i=0;
Set RESULT='';
Set NUMBERS='one two three four five six seven eight nine ten eleven tweleve thirteen fourteen fifteen sixteen seventeen eighteen nineteen twenty thirty forty fifty sixty seventy eighty ninety ';
Set s=LPAD(MONEY,15,'0');
Set billion=Substring(s,1,3);
Set million=Substring(s,4,3);
Set thousand=Substring(s,7,3);
IF MONEY= 0 THEN
SET RESULT =CONCAT('zero');
ELSE
WHILE i<=3 DO
Set hundreds=Substring(s,i*3+1,1);
Set tenth=Substring(s,i*3+2,1);
Set one=(Case tenth When 1 Then 10 Else 0 End)+Substring(s,i*3+3,1);
Set tenth=(Case When tenth<=1 Then 0 Else tenth End);
-- SET result=concat(result,'zheliten',tenth,' zhelione ',one,'zheli hundreds',hundreds);
IF (i=3 and (billion>0 or million>0 or thousand>0) and (hundreds=0 and (tenth>0 or one>0))) THEN
Set result=CONCAT(trim(result),' and ');
END IF;
-- Set result=CONCAT(result,billion,'billion','zhe',hundreds,'hundreds');
IF hundreds>0 THEN
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,hundreds*10-9,10)),' hundred ');
IF one>=1 THEN
Set result=CONCAT(trim(result),' and ');
END IF;
END IF;
IF tenth>=2 and tenth<=9 THEN
Set result=CONCAT(result,TRIM(Substring(numbers,tenth*10+171,10)));
IF one>=1 and one<=9 THEN
Set result=CONCAT(trim(result),'-',TRIM(Substring(numbers,one*10-9,10)));
END IF;
ELSEIF tenth=0 and one>=1 and one<=19 then
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,one*10-9,10)));
END IF;
IF i=0 and billion>0 THEN
Set result=CONCAT(trim(result),' billion ');
END IF;
IF i=1 and million>0 THEN
Set result=CONCAT(trim(result),' million ');
END IF;
IF i=2 and thousand>0 THEN
Set result=CONCAT(trim(result),' thousand ');
END IF;
set i=i+1;
END WHILE;
IF(result<>'') THEN
Set result=CONCAT(trim(result),' ');
END IF;
IF Substring(s,14,2)<>'00' THEN
Set tenth=Substring(s,14,1);
Set one=Substring(s,15,1);
IF(tenth>=2 and tenth<=9) THEN
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,tenth*10+171,10)));
END IF;
IF tenth=1 and one>=1 and one<=19 THEN
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,Substring(s,14,2)*10-9,10)));
ELSE
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,one*10-9,10)));
END IF;
END IF;
END IF;
RETURN RESULT;
END
测试数据的时候发现又有些缺陷,不仅是小数点之后的问题,还有对倒数两位数的转换也是有很大问题。
1.倒数两位数的tenth和one都比较简单,在这里应该对20以下的数字做个转换,转换成one的数据。
2.我采用小数点前的和小数点后分开转换,这边先贴上,第一种方法等会再加。
第二种方法比较好
CREATE DEFINER=`root`@`%` FUNCTION `NewProc`(MONEY DECIMAL( 15, 2 )) RETURNS varchar(400) CHARSET utf8
BEGIN
DECLARE result VARCHAR(400); -- 返回字符串
DECLARE i INT DEFAULT 0;
DECLARE hundreds INT;
DECLARE tenth INT;
DECLARE one INT;
DECLARE thousand INT;
DECLARE million INT;
DECLARE billion INT;
DECLARE NUMBERS VARCHAR(400);
DECLARE s VARCHAR( 18 );
-- set i=0;
Set RESULT='';
Set NUMBERS='one two three four five six seven eight nine ten eleven tweleve thirteen fourteen fifteen sixteen seventeen eighteen nineteen twenty thirty forty fifty sixty seventy eighty ninety ';
Set s=LPAD(MONEY,18,'0');
Set billion=Substring(s,1,3);
Set million=Substring(s,4,3);
Set thousand=Substring(s,7,3);
IF MONEY= 0 THEN
SET RESULT =CONCAT('zero');
ELSE
WHILE i<=3 DO
Set hundreds=Substring(s,i*3+1,1);
Set tenth=Substring(s,i*3+2,1);
Set one=(Case tenth When 1 Then 10 Else 0 End)+Substring(s,i*3+3,1);
Set tenth=(Case When tenth<=1 Then 0 Else tenth End);
-- SET result=concat(result,'zheliten',tenth,' zhelione ',one,'zheli hundreds',hundreds);
IF (i=3 and (billion>0 or million>0 or thousand>0) and (hundreds=0 and (tenth>0 or one>0))) THEN
Set result=CONCAT(trim(result),' and ');
END IF;
-- Set result=CONCAT(result,billion,'billion','zhe',hundreds,'hundreds');
IF hundreds>0 THEN
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,hundreds*10-9,10)),' hundred ');
IF one>=1 THEN
Set result=CONCAT(trim(result),' and ');
END IF;
END IF;
IF tenth>=2 and tenth<=9 THEN
Set result=CONCAT(result,TRIM(Substring(numbers,tenth*10+171,10)));
IF one>=1 and one<=9 THEN
Set result=CONCAT(trim(result),'-',TRIM(Substring(numbers,one*10-9,10)));
END IF;
ELSEIF tenth=0 and one>=1 and one<=19 then
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,one*10-9,10)));
END IF;
IF i=0 and billion>0 THEN
Set result=CONCAT(trim(result),' billion ');
END IF;
IF i=1 and million>0 THEN
Set result=CONCAT(trim(result),' million ');
END IF;
IF i=2 and thousand>0 THEN
Set result=CONCAT(trim(result),' thousand ');
END IF;
set i=i+1;
END WHILE;
IF Substring(s,14,2)<>'00' THEN
Set result=CONCAT(trim(result),' ','point');
Set tenth=Substring(s,17,1);
Set one=Substring(s,18,1);
IF(tenth<>'0') THEN
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,tenth*10-9,10)));
ELSE
Set result=CONCAT(trim(result),' ','zero');
END IF;
Set result=CONCAT(trim(result),' ',TRIM(Substring(numbers,one*10-9,10)));
END IF;
END IF;
RETURN RESULT;
END
CREATE DEFINER=`root`@`%` FUNCTION `EngPoint`(MONEY DECIMAL( 15, 2 )) RETURNS varchar(400) CHARSET utf8
BEGIN
DECLARE result VARCHAR(400); -- 返回字符串
DECLARE i INT DEFAULT 0;
DECLARE j INT DEFAULT 0;
DECLARE hundreds INT;
DECLARE tenth INT;
DECLARE one INT;
DECLARE cent INT;
DECLARE thousand INT;
DECLARE million INT;
DECLARE billion INT;
DECLARE NUMBERS VARCHAR(400);
DECLARE s VARCHAR( 15);
DECLARE integers varchar(15);
DECLARE fraction varchar(2);
Set RESULT='';
Set NUMBERS='one two three four five six seven eight nine ten eleven tweleve thirteen fourteen fifteen sixteen seventeen eighteen nineteen twenty thirty forty fifty sixty seventy eighty ninety ';
Set s=LPAD(MONEY,15,'0');
Set fraction=SUBSTRING_INDEX(s,'.',-1);
Set integers=SUBSTRING_INDEX(s,'.',1);
Set billion=Substring(integers,1,3);
Set million=Substring(integers,4,3);
Set thousand=Substring(integers,7,3);
IF integers= 0 THEN
SET RESULT =CONCAT('zero');
ELSE
WHILE i<=3 DO
Set hundreds=Substring(integers,i*3+1,1);
Set tenth=Substring(integers,i*3+2,1);
Set one=(Case tenth When 1 Then 10 Else 0 End)+Substring(integers,i*3+3,1);
Set tenth=(Case When tenth<=1 Then 0 Else tenth End);
IF (i=3 and (billion>0 or million>0 or thousand>0) and (hundreds=0 and (tenth>0 or one>0))) THEN
Set result=CONCAT(trim(result),' and ');
END IF;
IF hundreds>0 THEN
Set result=CONCAT(trim(result),' ',TRIM(Substring(NUMBERS,hundreds*10-9,10)),' hundred ');
IF one>=1 THEN
Set result=CONCAT(trim(result),' and ');
END IF;
END IF;
IF tenth>=2 and tenth<=9 THEN
Set result=CONCAT(result,TRIM(Substring(NUMBERS,tenth*10+171,10)));
IF one>=1 and one<=9 THEN
Set result=CONCAT(trim(result),'-',TRIM(Substring(NUMBERS,one*10-9,10)));
END IF;
ELSEIF tenth=0 and one>=1 and one<=19 then
Set result=CONCAT(trim(result),' ',TRIM(Substring(NUMBERS,one*10-9,10)));
END IF;
IF i=0 and billion>0 THEN
Set result=CONCAT(trim(result),' billion ');
END IF;
IF i=1 and million>0 THEN
Set result=CONCAT(trim(result),' million ');
END IF;
IF i=2 and thousand>0 THEN
Set result=CONCAT(trim(result),' thousand ');
END IF;
set i=i+1;
END WHILE;
END IF;
IF (fraction<>0) THEN
Set result=CONCAT(trim(result),' ','point');
WHILE j<2 DO
set cent =SUBSTRING(fraction,j+1,1);
IF cent<>'0' THEN
Set result=CONCAT(trim(result),' ',TRIM(Substring(NUMBERS,cent*10-9,10)));
ELSE
Set result=CONCAT(trim(result),' ','zero');
END IF;
set j=j+1;
END WHILE;
END IF;
Set result=CONCAT(trim(result),' ','ONLY');
RETURN RESULT;
END
不清楚的可以随时问,看到会回复