oracle 在 MYSQL 结果中将数字转换为单词！使用查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16435879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-19 01:39:02 来源:igfitidea点击:
Converting Numbers to Words in MYSQL result! Using Query
提问by DonOfDen
I am trying to print a output as follows when I execute a query in mysql.
当我在 mysql 中执行查询时，我试图按如下方式打印输出。
Name Salary Sal_in_Words
Mohan 45000 Rupees Forty Five Thousand Only
The column Salary has a value 45000 and in the third column the Value in second colunm is converted to words through Query.
Salary 列的值为 45000，在第三列中，第二列中的值通过 Query 转换为单词。
I found some article where in Oracle we can get the result as above using the below query:
我找到了一些文章，在 Oracle 中，我们可以使用以下查询获得上述结果：
select Salary, (' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.')) Sal_in_Words from employee
OUTPUT:
输出：
Name Salary Sal_in_Words
Suresh 45000 Rupees Forty Five Thousand Only
In MySQL I tried LPAD,RPAD but those just adds strings to the result not converting words to string.
在 MySQL 中，我尝试了 LPAD、RPAD，但这些只是将字符串添加到结果中，而不是将单词转换为字符串。
I found some tutorial but all those explained about "TO_CHAR (dates)".
我找到了一些教程，但所有这些都解释了“TO_CHAR（日期）”。
Is there any way to do it?
有什么办法吗？
采纳答案by user2155756
PROCEDURE TO GENERATE NUMBER TO WORDS FORMAT...........
CREATE DEFINER=`root`@`localhost` PROCEDURE `xnum2txt`(in nNum int(8))
BEGIN
declare cRetVal,cTxt,cX1,cX2 varchar(500);
set nNum=floor(nNum);
set cRetval='';
set cTxt='';
if nnum<0 then
set cRetval="-"+ xNum22Txt(nnum*-1);
elseif nNum<11 then
set cTxt="Zero One Two ThreeFour Five Six SevenEightNine Ten ";
set cRetVal= trim(substring(cTxt,(nNum * 5) + 1,5)) ;
ELSEIF nNum < 20 then
set cTxt = "Eleven Twelve Thirteen Fourteen Fifteen Sixteen SeventeenEighteen Nineteen ";
set cRetval = trim(substring(cTxt,((nNum - 11) * 9) + 1,9));
ELSEIF nNum < 30 then
set cRetval = "Twenty" + if(nNum > 20,"-" + xnum22txt(nNum-20)," ");
ELSEIF nNum < 40 then
set cRetval = "Thirty" + if(nNum > 30,"-" + xnum22txt(nNum-30)," ");
ELSEIF nNum < 50 then
set cRetval = "Forty" + if(nNum > 40,"-" + xnum22txt(nNum-40)," ");
ELSEIF nNum < 60 then
set cRetval = "Fifty" + if(nNum > 50,"-" + xnum22txt(nNum-50)," ");
ELSEIF nNum < 70 then
set cRetval = "Sixty" + if(nNum > 60,"-" + xnum22txt(nNum-60)," ");
ELSEIF nNum < 80 then
set cRetval = "Seventy" + if(nNum > 70,"-" + xnum22txt(nNum-70)," ");
ELSEIF nNum < 90 then
set cRetval = "Eighty" + if(nNum > 80,"-" + xnum22txt(nNum-80)," ");
ELSEIF nNum < 100 then
set cRetval = "Ninety" + if(nNum > 90,"-" + xnum22txt(nNum-90)," ");
ELSEIF nNum < 1000 then
set cX1 = floor(nNum/100);
set cX2 = nNum-(cX1*100);
set cRetval = xnum22txt(cX1) + "Hundred" + if(cX2 > 0," " + xnum22txt(cX2)," ");
ELSEIF nNum < 1000000 then
set cX1 = floor(nNum/1000);
set cX2 = nNum-(cX1*1000);
set cRetval = xnum22txt(cX1) + "Thousand" + if(cX2 > 0,", " + xnum22txt(cX2)," ");
ELSEIF nNum < 1000000000 then
set cX1 = floor(nNum/1000000);
set cX2 = nNum-(cX1*1000000);
set cRetval = xnum22txt(cX1) + "Million" + if(cX2 > 0,", " + xnum22txt(cX2)," ");
ELSEIF nNum < 1000000000000 then
set cX1 = floor(nNum/1000000000);
set cX2 = nNum-(cX1*1000000000);
set cRetval = xnum22txt(cX1) + "Billion" + if(cX2 > 0,", " + xnum22txt(cX2)," ");
ELSE
set cRetval = "***";
end if;
select cRetVal;
END
回答by DonOfDen
There is not simple function in MySQL you need to write a function with the help of the function you can achieve this result.
MySQL 中没有简单的函数需要自己写一个函数，借助函数就可以达到这样的效果。
Check the following.. It works for me.. Reference
检查以下..它对我有用..参考
DELIMITER $$
CREATE FUNCTION `number_to_string`(n INT) RETURNS varchar(100)
BEGIN
-- This function returns the string representation of a number.
-- It's just an example... I'll restrict it to hundreds, but
-- it can be extended easily.
-- The idea is:
-- For each digit you need a position,
-- For each position, you assign a string
declare ans varchar(100);
declare dig1, dig2, dig3 int; -- (one variable per digit)
set ans = '';
set dig3 = floor(n / 100);
set dig2 = floor(n / 10) - dig3*10;
set dig1 = n - (dig3*100 + dig2*10);
if dig3 > 0 then
case
when dig3=1 then set ans=concat(ans, 'one hundred');
when dig3=2 then set ans=concat(ans, 'two hundred');
when dig3=3 then set ans=concat(ans, 'three hundred');
when dig3=4 then set ans=concat(ans, 'four hundred');
when dig3=5 then set ans=concat(ans, 'five hundred');
when dig3=6 then set ans=concat(ans, 'six hundred');
when dig3=7 then set ans=concat(ans, 'seven hundred');
when dig3=8 then set ans=concat(ans, 'eight hundred');
when dig3=9 then set ans=concat(ans, 'nine hundred');
else set ans = ans;
end case;
end if;
if dig2 = 1 then
case
when (dig2*10 + dig1) = 10 then set ans=concat(ans,' ten');
when (dig2*10 + dig1) = 11 then set ans=concat(ans,' eleven');
when (dig2*10 + dig1) = 12 then set ans=concat(ans,' twelve');
when (dig2*10 + dig1) = 13 then set ans=concat(ans,' thirteen');
when (dig2*10 + dig1) = 14 then set ans=concat(ans,' fourteen');
when (dig2*10 + dig1) = 15 then set ans=concat(ans,' fifteen');
when (dig2*10 + dig1) = 16 then set ans=concat(ans,' sixteen');
when (dig2*10 + dig1) = 17 then set ans=concat(ans,' seventeen');
when (dig2*10 + dig1) = 18 then set ans=concat(ans,' eighteen');
when (dig2*10 + dig1) = 19 then set ans=concat(ans,' nineteen');
else set ans=ans;
end case;
else
if dig2 > 0 then
case
when dig2=2 then set ans=concat(ans, ' twenty');
when dig2=3 then set ans=concat(ans, ' thirty');
when dig2=4 then set ans=concat(ans, ' fourty');
when dig2=5 then set ans=concat(ans, ' fifty');
when dig2=6 then set ans=concat(ans, ' sixty');
when dig2=7 then set ans=concat(ans, ' seventy');
when dig2=8 then set ans=concat(ans, ' eighty');
when dig2=9 then set ans=concat(ans, ' ninety');
else set ans=ans;
end case;
end if;
if dig1 > 0 then
case
when dig1=1 then set ans=concat(ans, ' one');
when dig1=2 then set ans=concat(ans, ' two');
when dig1=3 then set ans=concat(ans, ' three');
when dig1=4 then set ans=concat(ans, ' four');
when dig1=5 then set ans=concat(ans, ' five');
when dig1=6 then set ans=concat(ans, ' six');
when dig1=7 then set ans=concat(ans, ' seven');
when dig1=8 then set ans=concat(ans, ' eight');
when dig1=9 then set ans=concat(ans, ' nine');
else set ans=ans;
end case;
end if;
end if;
return trim(ans);
END$$
DELIMITER ;
If get the following error..
如果出现以下错误..
#1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
Run this query:
运行此查询：
SET GLOBAL log_bin_trust_function_creators = 1;
After that Create a function in Mysql:
之后在Mysql中创建一个函数：
Call the function Just like the below command:
调用函数就像下面的命令：
SELECT number_to_string( 666 );
You will get the output as:
您将获得如下输出：
number_to_string( 666 )
six hundred sixty six
Hope this will help for others!
希望这对其他人有帮助！
回答by Douglas.Sesar
I took TomPHP's answer and extended it to work up to 6 digits:
我接受了 TomPHP 的回答并将其扩展为最多 6 位数字：
SELECT number_into_words(999999);would result in nine hundred ninety nine thousand nine hundred ninety nine
SELECT number_into_words(999999);会导致 nine hundred ninety nine thousand nine hundred ninety nine
I also updated the initial setting of dig1through dig6so it was more readable to myself (I understand that there are more efficient ways to do this).
我还更新了dig1through的初始设置，dig6这样我自己就更易读了（我知道有更有效的方法来做到这一点）。
DELIMITER $$
CREATE FUNCTION `number_to_words`(n INT) RETURNS varchar(100)
BEGIN
-- This function returns the string representation of a number.
-- It's just an example... I'll restrict it to hundreds, but
-- it can be extended easily.
-- The idea is:
-- For each digit you need a position,
-- For each position, you assign a string
declare ans varchar(100);
declare dig1, dig2, dig3, dig4, dig5, dig6 int;
set ans = '';
set dig6 = CAST(RIGHT(CAST(floor(n / 100000) as CHAR(8)), 1) as SIGNED);
set dig5 = CAST(RIGHT(CAST(floor(n / 10000) as CHAR(8)), 1) as SIGNED);
set dig4 = CAST(RIGHT(CAST(floor(n / 1000) as CHAR(8)), 1) as SIGNED);
set dig3 = CAST(RIGHT(CAST(floor(n / 100) as CHAR(8)), 1) as SIGNED);
set dig2 = CAST(RIGHT(CAST(floor(n / 10) as CHAR(8)), 1) as SIGNED);
set dig1 = CAST(RIGHT(floor(n), 1) as SIGNED);
if dig6 > 0 then
case
when dig6=1 then set ans=concat(ans, 'one hundred');
when dig6=2 then set ans=concat(ans, 'two hundred');
when dig6=3 then set ans=concat(ans, 'three hundred');
when dig6=4 then set ans=concat(ans, 'four hundred');
when dig6=5 then set ans=concat(ans, 'five hundred');
when dig6=6 then set ans=concat(ans, 'six hundred');
when dig6=7 then set ans=concat(ans, 'seven hundred');
when dig6=8 then set ans=concat(ans, 'eight hundred');
when dig6=9 then set ans=concat(ans, 'nine hundred');
else set ans = ans;
end case;
end if;
if dig5 = 1 then
case
when (dig5*10 + dig4) = 10 then set ans=concat(ans, ' ten thousand ');
when (dig5*10 + dig4) = 11 then set ans=concat(ans, ' eleven thousand ');
when (dig5*10 + dig4) = 12 then set ans=concat(ans, ' twelve thousand ');
when (dig5*10 + dig4) = 13 then set ans=concat(ans, ' thirteen thousand ');
when (dig5*10 + dig4) = 14 then set ans=concat(ans, ' fourteen thousand ');
when (dig5*10 + dig4) = 15 then set ans=concat(ans, ' fifteen thousand ');
when (dig5*10 + dig4) = 16 then set ans=concat(ans, ' sixteen thousand ');
when (dig5*10 + dig4) = 17 then set ans=concat(ans, ' seventeen thousand ');
when (dig5*10 + dig4) = 18 then set ans=concat(ans, ' eighteen thousand ');
when (dig5*10 + dig4) = 19 then set ans=concat(ans, ' nineteen thousand ');
else set ans=ans;
end case;
else
if dig5 > 0 then
case
when dig5=2 then set ans=concat(ans, ' twenty');
when dig5=3 then set ans=concat(ans, ' thirty');
when dig5=4 then set ans=concat(ans, ' fourty');
when dig5=5 then set ans=concat(ans, ' fifty');
when dig5=6 then set ans=concat(ans, ' sixty');
when dig5=7 then set ans=concat(ans, ' seventy');
when dig5=8 then set ans=concat(ans, ' eighty');
when dig5=9 then set ans=concat(ans, ' ninety');
else set ans=ans;
end case;
end if;
if dig4 > 0 then
case
when dig4=1 then set ans=concat(ans, ' one thousand ');
when dig4=2 then set ans=concat(ans, ' two thousand ');
when dig4=3 then set ans=concat(ans, ' three thousand ');
when dig4=4 then set ans=concat(ans, ' four thousand ');
when dig4=5 then set ans=concat(ans, ' five thousand ');
when dig4=6 then set ans=concat(ans, ' six thousand ');
when dig4=7 then set ans=concat(ans, ' seven thousand ');
when dig4=8 then set ans=concat(ans, ' eight thousand ');
when dig4=9 then set ans=concat(ans, ' nine thousand ');
else set ans=ans;
end case;
end if;
if dig4 = 0 AND (dig5 != 0 || dig6 != 0) then
set ans=concat(ans, ' thousand ');
end if;
end if;
if dig3 > 0 then
case
when dig3=1 then set ans=concat(ans, 'one hundred');
when dig3=2 then set ans=concat(ans, 'two hundred');
when dig3=3 then set ans=concat(ans, 'three hundred');
when dig3=4 then set ans=concat(ans, 'four hundred');
when dig3=5 then set ans=concat(ans, 'five hundred');
when dig3=6 then set ans=concat(ans, 'six hundred');
when dig3=7 then set ans=concat(ans, 'seven hundred');
when dig3=8 then set ans=concat(ans, 'eight hundred');
when dig3=9 then set ans=concat(ans, 'nine hundred');
else set ans = ans;
end case;
end if;
if dig2 = 1 then
case
when (dig2*10 + dig1) = 10 then set ans=concat(ans, ' ten');
when (dig2*10 + dig1) = 11 then set ans=concat(ans, ' eleven');
when (dig2*10 + dig1) = 12 then set ans=concat(ans, ' twelve');
when (dig2*10 + dig1) = 13 then set ans=concat(ans, ' thirteen');
when (dig2*10 + dig1) = 14 then set ans=concat(ans, ' fourteen');
when (dig2*10 + dig1) = 15 then set ans=concat(ans, ' fifteen');
when (dig2*10 + dig1) = 16 then set ans=concat(ans, ' sixteen');
when (dig2*10 + dig1) = 17 then set ans=concat(ans, ' seventeen');
when (dig2*10 + dig1) = 18 then set ans=concat(ans, ' eighteen');
when (dig2*10 + dig1) = 19 then set ans=concat(ans, ' nineteen');
else set ans=ans;
end case;
else
if dig2 > 0 then
case
when dig2=2 then set ans=concat(ans, ' twenty');
when dig2=3 then set ans=concat(ans, ' thirty');
when dig2=4 then set ans=concat(ans, ' fourty');
when dig2=5 then set ans=concat(ans, ' fifty');
when dig2=6 then set ans=concat(ans, ' sixty');
when dig2=7 then set ans=concat(ans, ' seventy');
when dig2=8 then set ans=concat(ans, ' eighty');
when dig2=9 then set ans=concat(ans, ' ninety');
else set ans=ans;
end case;
end if;
if dig1 > 0 then
case
when dig1=1 then set ans=concat(ans, ' one');
when dig1=2 then set ans=concat(ans, ' two');
when dig1=3 then set ans=concat(ans, ' three');
when dig1=4 then set ans=concat(ans, ' four');
when dig1=5 then set ans=concat(ans, ' five');
when dig1=6 then set ans=concat(ans, ' six');
when dig1=7 then set ans=concat(ans, ' seven');
when dig1=8 then set ans=concat(ans, ' eight');
when dig1=9 then set ans=concat(ans, ' nine');
else set ans=ans;
end case;
end if;
end if;
return trim(ans);
END
$$
回答by Sharad Pawar
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `number_to_words`(`n` DECIMAL(10,2)) RETURNS varchar(100) CHARSET latin1
BEGIN
declare ans varchar(100);
declare dig1, dig2, dig3, dig4, dig5, dig6 int;
DECLARE num1,num2 varchar(100);
set ans = '';
IF n != '' THEN // Check is Blank Argument
set num1 = SUBSTRING_INDEX(n, ".", 1);
set num2 = SUBSTRING_INDEX(n, '.', -1);
set dig6 = CAST(RIGHT(CAST(floor(num1 / 100000) as CHAR(8)), 1) as SIGNED);
set dig5 = CAST(RIGHT(CAST(floor(num1 / 10000) as CHAR(8)), 1) as SIGNED);
set dig4 = CAST(RIGHT(CAST(floor(num1 / 1000) as CHAR(8)), 1) as SIGNED);
set dig3 = CAST(RIGHT(CAST(floor(num1 / 100) as CHAR(8)), 1) as SIGNED);
set dig2 = CAST(RIGHT(CAST(floor(num1 / 10) as CHAR(8)), 1) as SIGNED);
set dig1 = CAST(RIGHT(floor(num1), 1) as SIGNED);
IF num1 != '' then
if dig6 > 0 then
case
when dig6=1 then set ans=concat(ans, 'One Hundred');
when dig6=2 then set ans=concat(ans, 'Two Hundred');
when dig6=3 then set ans=concat(ans, 'Three Hundred');
when dig6=4 then set ans=concat(ans, 'Four Hundred');
when dig6=5 then set ans=concat(ans, 'Five Hundred');
when dig6=6 then set ans=concat(ans, 'Six Hundred');
when dig6=7 then set ans=concat(ans, 'Seven Hundred');
when dig6=8 then set ans=concat(ans, 'Eight Hundred');
when dig6=9 then set ans=concat(ans, 'Nine Hundred');
else set ans = ans;
end case;
end if;
if dig5 = 1 then
case
when (dig5*10 + dig4) = 10 then set ans=concat(ans, ' Ten Thousand ');
when (dig5*10 + dig4) = 11 then set ans=concat(ans, ' Eleven Thousand ');
when (dig5*10 + dig4) = 12 then set ans=concat(ans, ' Twelve Thousand ');
when (dig5*10 + dig4) = 13 then set ans=concat(ans, ' Thirteen Thousand ');
when (dig5*10 + dig4) = 14 then set ans=concat(ans, ' Fourteen Thousand ');
when (dig5*10 + dig4) = 15 then set ans=concat(ans, ' Fifteen Thousand ');
when (dig5*10 + dig4) = 16 then set ans=concat(ans, ' Sixteen Thousand ');
when (dig5*10 + dig4) = 17 then set ans=concat(ans, ' Seventeen Thousand ');
when (dig5*10 + dig4) = 18 then set ans=concat(ans, ' Eighteen Thousand ');
when (dig5*10 + dig4) = 19 then set ans=concat(ans, ' Nineteen Thousand ');
else set ans=ans;
end case;
else
if dig5 > 0 then
case
when dig5=2 then set ans=concat(ans, ' Twenty');
when dig5=3 then set ans=concat(ans, ' Thirty');
when dig5=4 then set ans=concat(ans, ' Fourty');
when dig5=5 then set ans=concat(ans, ' Fifty');
when dig5=6 then set ans=concat(ans, ' Sixty');
when dig5=7 then set ans=concat(ans, ' Seventy');
when dig5=8 then set ans=concat(ans, ' Eighty');
when dig5=9 then set ans=concat(ans, ' Ninety');
else set ans=ans;
end case;
end if;
if dig4 > 0 then
case
when dig4=1 then set ans=concat(ans, ' One Thousand ');
when dig4=2 then set ans=concat(ans, ' Two Thousand ');
when dig4=3 then set ans=concat(ans, ' Three Thousand ');
when dig4=4 then set ans=concat(ans, ' Four Thousand ');
when dig4=5 then set ans=concat(ans, ' Five Thousand ');
when dig4=6 then set ans=concat(ans, ' Six Thousand ');
when dig4=7 then set ans=concat(ans, ' Seven Thousand ');
when dig4=8 then set ans=concat(ans, ' Eight Thousand ');
when dig4=9 then set ans=concat(ans, ' Nine Thousand ');
else set ans=ans;
end case;
end if;
if dig4 = 0 AND (dig5 != 0 || dig6 != 0) then
set ans=concat(ans, ' Thousand ');
end if;
end if;
if dig3 > 0 then
case
when dig3=1 then set ans=concat(ans, 'One Hundred');
when dig3=2 then set ans=concat(ans, 'Two Hundred');
when dig3=3 then set ans=concat(ans, 'Three Hundred');
when dig3=4 then set ans=concat(ans, 'Four Hundred');
when dig3=5 then set ans=concat(ans, 'Five Hundred');
when dig3=6 then set ans=concat(ans, 'Six Hundred');
when dig3=7 then set ans=concat(ans, 'Seven Hundred');
when dig3=8 then set ans=concat(ans, 'Eight Hundred');
when dig3=9 then set ans=concat(ans, 'Nine Hundred');
else set ans = ans;
end case;
end if;
if dig2 = 1 then
case
when (dig2*10 + dig1) = 10 then set ans=concat(ans, ' Ten');
when (dig2*10 + dig1) = 11 then set ans=concat(ans, ' Eleven');
when (dig2*10 + dig1) = 12 then set ans=concat(ans, ' Twelve');
when (dig2*10 + dig1) = 13 then set ans=concat(ans, ' Thirteen');
when (dig2*10 + dig1) = 14 then set ans=concat(ans, ' Fourteen');
when (dig2*10 + dig1) = 15 then set ans=concat(ans, ' Fifteen');
when (dig2*10 + dig1) = 16 then set ans=concat(ans, ' Sixteen');
when (dig2*10 + dig1) = 17 then set ans=concat(ans, ' Seventeen');
when (dig2*10 + dig1) = 18 then set ans=concat(ans, ' Eighteen');
when (dig2*10 + dig1) = 19 then set ans=concat(ans, ' Nineteen');
else set ans=ans;
end case;
else
if dig2 > 0 then
case
when dig2=2 then set ans=concat(ans, ' Twenty');
when dig2=3 then set ans=concat(ans, ' Thirty');
when dig2=4 then set ans=concat(ans, ' Fourty');
when dig2=5 then set ans=concat(ans, ' Fifty');
when dig2=6 then set ans=concat(ans, ' Sixty');
when dig2=7 then set ans=concat(ans, ' Seventy');
when dig2=8 then set ans=concat(ans, ' Eighty');
when dig2=9 then set ans=concat(ans, ' Ninety');
else set ans=ans;
end case;
end if;
if dig1 >= 0 then
case
when dig1=0 then set ans=concat(ans, ' Zero');
when dig1=1 then set ans=concat(ans, ' One');
when dig1=2 then set ans=concat(ans, ' Two');
when dig1=3 then set ans=concat(ans, ' Three');
when dig1=4 then set ans=concat(ans, ' Four');
when dig1=5 then set ans=concat(ans, ' Five');
when dig1=6 then set ans=concat(ans, ' Six');
when dig1=7 then set ans=concat(ans, ' Seven');
when dig1=8 then set ans=concat(ans, ' Eight');
when dig1=9 then set ans=concat(ans, ' Nine');
else set ans=ans;
end case;
end if;
end if;
end if;
IF num2 > 0 then
set dig2 = CAST(RIGHT(CAST(floor(num2 / 10) as CHAR(8)), 1) as SIGNED);
set dig1 = CAST(RIGHT(floor(num2), 1) as SIGNED);
set ans=concat(ans, ' POINT ');
if dig2 > 0 then
case
when dig2=2 then set ans=concat(ans, ' Twenty');
when dig2=3 then set ans=concat(ans, ' Thirty');
when dig2=4 then set ans=concat(ans, ' Fourty');
when dig2=5 then set ans=concat(ans, ' Fifty');
when dig2=6 then set ans=concat(ans, ' Sixty');
when dig2=7 then set ans=concat(ans, ' Seventy');
when dig2=8 then set ans=concat(ans, ' Eighty');
when dig2=9 then set ans=concat(ans, ' Ninety');
else set ans=ans;
end case;
end if;
if dig1 > 0 then
case
when dig1=0 then set ans=concat(ans, ' Zero');
when dig1=1 then set ans=concat(ans, ' One');
when dig1=2 then set ans=concat(ans, ' Two');
when dig1=3 then set ans=concat(ans, ' Three');
when dig1=4 then set ans=concat(ans, ' Four');
when dig1=5 then set ans=concat(ans, ' five');
when dig1=6 then set ans=concat(ans, ' Six');
when dig1=7 then set ans=concat(ans, ' Seven');
when dig1=8 then set ans=concat(ans, ' Eight');
when dig1=9 then set ans=concat(ans, ' Nine');
else set ans=ans;
end case;
end if;
END IF;
END IF;
return trim(ans);
END$$
DELIMITER ;
回答by selvakumar
Mysql Function in Indian Standard
印度标准中的 Mysql 函数
CREATE DEFINER=`root`@`%` FUNCTION `number_to_string`(n INT) RETURNS varchar(1000) CHARSET latin1
BEGIN
declare ans varchar(1000);
declare dig1, dig2, dig3,dig4,dig5,dig6,dig7,dig8,dig9 int;
set ans = '';
set dig9 = floor(n/100000000);
set dig8 = floor(n/10000000) - dig9*10;
set dig7 = floor(n/1000000) -(floor(n/10000000)*10);
set dig6 = floor(n/100000) - (floor(n/1000000)*10);
set dig5 = floor(n/10000) - (floor(n/100000)*10);
set dig4 = floor(n/1000) - (floor(n/10000)*10);
set dig3 = floor(n/100) - (floor(n/1000)*10);
set dig2 = floor(n/10) - (floor(n/100)*10);
set dig1 = n - (floor(n / 10)*10);
if dig7 = 1 then
case
when (dig7*10 + dig6) = 10 then set ans=concat(ans,'Ten Lakhs');
when (dig7*10 + dig6) = 11 then set ans=concat(ans,'Eleven Lakhs');
when (dig7*10 + dig6) = 12 then set ans=concat(ans,'Twelve Lakhs');
when (dig7*10 + dig6) = 13 then set ans=concat(ans,'Thirteen Lakhs');
when (dig7*10 + dig6) = 14 then set ans=concat(ans,'Fourteen Lakhs');
when (dig7*10 + dig6) = 15 then set ans=concat(ans,'Fifteen Lakhs');
when (dig7*10 + dig6) = 16 then set ans=concat(ans,'Sixteen Lakhs');
when (dig7*10 + dig6) = 17 then set ans=concat(ans,'Seventeen Lakhs');
when (dig7*10 + dig6) = 18 then set ans=concat(ans,'Eighteen Lakhs');
when (dig7*10 + dig6) = 19 then set ans=concat(ans,'Nineteen Lakhs');
else set ans=ans;
end case;
else
if dig7 > 0 then
case
when dig7=2 then set ans=concat(ans, ' Twenty');
when dig7=3 then set ans=concat(ans, ' Thirty');
when dig7=4 then set ans=concat(ans, ' Fourty');
when dig7=5 then set ans=concat(ans, ' Fifty');
when dig7=6 then set ans=concat(ans, ' Sixty');
when dig7=7 then set ans=concat(ans, ' Seventy');
when dig7=8 then set ans=concat(ans, ' Eighty');
when dig7=9 then set ans=concat(ans, ' Ninety');
else set ans=ans;
end case;
if ans <> '' and dig6 =0 then
set ans=concat(ans, ' Thousand');
end if;
end if;
if ans <> '' and dig6 > 0 and dig7 =0 then
set ans=concat(ans, ' And ');
end if;
if dig6 > 0 then
case
when dig6=1 then set ans=concat(ans, ' One Lakhs');
when dig6=2 then set ans=concat(ans, ' Two Lakhs');
when dig6=3 then set ans=concat(ans, ' Three Lakhs');
when dig6=4 then set ans=concat(ans, ' Four Lakhs');
when dig6=5 then set ans=concat(ans, ' Five Lakhs');
when dig6=6 then set ans=concat(ans, ' Six Lakhs');
when dig6=7 then set ans=concat(ans, ' Seven Lakhs');
when dig6=8 then set ans=concat(ans, ' Eight Lakhs');
when dig6=9 then set ans=concat(ans, ' Nine Lakhs');
else set ans = ans;
end case;
end if;
end if;
if ans <> '' and dig5 > 0 then
set ans=concat(ans, '');
end if;
if dig5 = 1 then
case
when (dig5*10 + dig4) = 10 then set ans=concat(ans,'Ten Thousand');
when (dig5*10 + dig4) = 11 then set ans=concat(ans,'Eleven Thousand');
when (dig5*10 + dig4) = 12 then set ans=concat(ans,'Twelve Thousand');
when (dig5*10 + dig4) = 13 then set ans=concat(ans,'Thirteen Thousand');
when (dig5*10 + dig4) = 14 then set ans=concat(ans,'Fourteen Thousand');
when (dig5*10 + dig4) = 15 then set ans=concat(ans,'Fifteen Thousand');
when (dig5*10 + dig4) = 16 then set ans=concat(ans,'Sixteen Thousand');
when (dig5*10 + dig4) = 17 then set ans=concat(ans,'Seventeen Thousand');
when (dig5*10 + dig4) = 18 then set ans=concat(ans,'Eighteen Thousand');
when (dig5*10 + dig4) = 19 then set ans=concat(ans,'Nineteen Thousand');
else set ans=ans;
end case;
else
if dig5 > 0 then
case
when dig5=2 then set ans=concat(ans, ' Twenty');
when dig5=3 then set ans=concat(ans, ' Thirty');
when dig5=4 then set ans=concat(ans, ' Fourty');
when dig5=5 then set ans=concat(ans, ' Fifty');
when dig5=6 then set ans=concat(ans, ' Sixty');
when dig5=7 then set ans=concat(ans, ' Seventy');
when dig5=8 then set ans=concat(ans, ' Eighty');
when dig5=9 then set ans=concat(ans, ' Ninety');
else set ans=ans;
end case;
if ans <> '' and dig4 =0 then
set ans=concat(ans, ' Thousand');
end if;
end if;
if ans <> '' and dig4 > 0 and dig5 =0 then
set ans=concat(ans, ' And ');
end if;
if dig4 > 0 then
case
when dig4=1 then set ans=concat(ans, ' One Thousand');
when dig4=2 then set ans=concat(ans, ' Two Thousand');
when dig4=3 then set ans=concat(ans, ' Three Thousand');
when dig4=4 then set ans=concat(ans, ' Four Thousand');
when dig4=5 then set ans=concat(ans, ' Five Thousand');
when dig4=6 then set ans=concat(ans, ' Six Thousand');
when dig4=7 then set ans=concat(ans, ' Seven Thousand');
when dig4=8 then set ans=concat(ans, ' Eight Thousand');
when dig4=9 then set ans=concat(ans, ' Nine Thousand');
else set ans = ans;
end case;
end if;
end if;
if ans <> '' and dig3 > 0 then
set ans=concat(ans, ' And ');
end if;
if dig3 > 0 then
case
when dig3=1 then set ans=concat(ans, 'One Hundred');
when dig3=2 then set ans=concat(ans, 'Two Hundred');
when dig3=3 then set ans=concat(ans, 'Three Hundred');
when dig3=4 then set ans=concat(ans, 'Four Hundred');
when dig3=5 then set ans=concat(ans, 'Five Hundred');
when dig3=6 then set ans=concat(ans, 'Six Hundred');
when dig3=7 then set ans=concat(ans, 'Seven Hundred');
when dig3=8 then set ans=concat(ans, 'Eight Hundred');
when dig3=9 then set ans=concat(ans, 'Nine Hundred');
else set ans = ans;
end case;
end if;
if ans <> '' and dig2 > 0 then
set ans=concat(ans, ' And ');
end if;
if dig2 = 1 then
case
when (dig2*10 + dig1) = 10 then set ans=concat(ans,'Ten');
when (dig2*10 + dig1) = 11 then set ans=concat(ans,'Eleven');
when (dig2*10 + dig1) = 12 then set ans=concat(ans,'Twelve');
when (dig2*10 + dig1) = 13 then set ans=concat(ans,'Thirteen');
when (dig2*10 + dig1) = 14 then set ans=concat(ans,'Fourteen');
when (dig2*10 + dig1) = 15 then set ans=concat(ans,'Fifteen');
when (dig2*10 + dig1) = 16 then set ans=concat(ans,'Sixteen');
when (dig2*10 + dig1) = 17 then set ans=concat(ans,'Seventeen');
when (dig2*10 + dig1) = 18 then set ans=concat(ans,'Eighteen');
when (dig2*10 + dig1) = 19 then set ans=concat(ans,'Nineteen');
else set ans=ans;
end case;
else
if dig2 > 0 then
case
when dig2=2 then set ans=concat(ans, ' Twenty');
when dig2=3 then set ans=concat(ans, ' Thirty');
when dig2=4 then set ans=concat(ans, ' Fourty');
when dig2=5 then set ans=concat(ans, ' Fifty');
when dig2=6 then set ans=concat(ans, ' Sixty');
when dig2=7 then set ans=concat(ans, ' Seventy');
when dig2=8 then set ans=concat(ans, ' Eighty');
when dig2=9 then set ans=concat(ans, ' Ninety');
else set ans=ans;
end case;
end if;
if ans <> '' and dig1 > 0 and dig2 =0 then
set ans=concat(ans, ' And ');
end if;
if dig1 > 0 then
case
when dig1=1 then set ans=concat(ans, ' One');
when dig1=2 then set ans=concat(ans, ' Two');
when dig1=3 then set ans=concat(ans, ' Three');
when dig1=4 then set ans=concat(ans, ' Four');
when dig1=5 then set ans=concat(ans, ' Five');
when dig1=6 then set ans=concat(ans, ' Six');
when dig1=7 then set ans=concat(ans, ' Seven');
when dig1=8 then set ans=concat(ans, ' Eight');
when dig1=9 then set ans=concat(ans, ' Nine');
else set ans=ans;
end case;
end if;
end if;
return trim(ans);
END
回答by lipasion
FUNCTION `fn_number2words`(_Number double(18,2))
RETURNS varchar(8000)
BEGIN
DECLARE _inputNumber VARCHAR(38);
DECLARE _outputString VARCHAR(8000);
DECLARE _length INT;
DECLARE _counter INT;
DECLARE _loops INT DEFAULT 0;
DECLARE _position INT;
DECLARE _chunk CHAR(3); -- for chunks of 3 numbers
DECLARE _tensones CHAR(2);
DECLARE _hundreds CHAR(1);
DECLARE _tens CHAR(1);
DECLARE _ones CHAR(1);
DECLARE _cents varchar(50);
DECLARE _numtmp1 varchar(50);
DECLARE _numtmp2 varchar(50);
DECLARE _numtmp3 varchar(50);
IF _Number = 0 THEN
SET _outputString = 'Zero';
ELSE
SET _cents = REPLACE(CAST((_Number % 1)*100 as char), '.00', '');
-- initialize the variables
SELECT REPLACE(CAST((_Number - (_Number % 1)) as char), '.00', ''),
'',
1
INTO _inputNumber, _outputString, _counter;
IF MOD(LENGTH(_inputNumber), 3) = 1 THEN
SET _inputNumber = CONCAT('00', _inputNumber);
ELSEIF MOD(LENGTH(_inputNumber), 3) = 2 THEN
SET _inputNumber = CONCAT('0', _inputNumber);
END IF;
SELECT LENGTH(_inputNumber),
(LENGTH(_inputNumber)/3),
LENGTH(_inputNumber)/3
INTO _length, _position, _loops;
WHILE _counter <= _loops DO
SET _chunk = SUBSTR(_inputNumber, ((_position-1)*3)+1, 3);
IF _chunk <> '000' THEN
SELECT
SUBSTR(_chunk, 2, 2), SUBSTR(_chunk, 1, 1), SUBSTR(_chunk, 2, 1), SUBSTR(_chunk, 3, 1)
INTO
_tensones,
_hundreds,
_tens,
_ones;
/*SELECT _inputNumber, _chunk, _tensones, _hundreds, _tens, _ones, _counter, _position, _loops, CAST(_tensones as UNSIGNED INT);*/
IF CAST(_tensones as UNSIGNED) <= 20 OR _ones='0' THEN
SELECT word INTO _numtmp1 FROM tbl_numbers WHERE number = _tensones;
SELECT CASE _counter
WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
INTO _numtmp2;
SET _outputString = CONCAT(_numtmp1, _numtmp2, _outputString);
ELSE
SELECT word INTO _numtmp1 FROM tbl_numbers WHERE number = CONCAT(_tens,'0');
SELECT word INTO _numtmp2 FROM tbl_numbers WHERE number = CONCAT('0',_ones);
SELECT CASE _counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
INTO _numtmp3;
SET _outputString = CONCAT(' ',_numtmp1, '-',_numtmp2, _numtmp3, _outputString);
END IF;
-- now get the hundreds
IF _hundreds <> '0' THEN
SELECT word INTO _numtmp1 FROM tbl_numbers WHERE number = CONCAT('0',_hundreds);
SET _outputString = CONCAT(_numtmp1, ' hundred ', _outputString);
END IF;
END IF;
/* seed variables */
SELECT (_counter+1), (_position-1)
INTO _counter, _position;
END WHILE;
END IF;
IF RTRIM(LTRIM(_outputString)) = '' THEN
SET _outputString = '';
ELSE
SET _outputString = CONCAT(_outputString, ' PESOS AND ');
END IF;
SET _outputString = UPPER(CONCAT(_outputString,_cents,'/100 CENT(S)')); -- return the result
RETURN _outputString;
END
