在MYSQL中将数字转换为单词！使用查询

小编典典

在MYSQL中将数字转换为单词！使用查询

sql

当我在mysql中执行查询时，我尝试按以下方式打印输出。

Name    Salary  Sal_in_Words
Mohan   45000   Rupees Forty Five Thousand Only

Salary列的值为45000，第三列中的第二列中的Value通过Query转换为单词。

我找到了一些文章，在Oracle中可以使用以下查询获得如上所述的结果：

select Salary, (' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.')) Sal_in_Words from employee

输出：

Name    Salary  Sal_in_Words
Suresh  45000   Rupees Forty Five Thousand Only

在MySQL中，我尝试了LPAD，RPAD，但那些只是将字符串添加到结果中，而不是将单词转换为字符串。

我找到了一些教程，但所有这些教程都对“ TO_CHAR（日期）”进行了说明。

有什么办法吗？

阅读 274

2021-03-10

共1个答案

小编典典

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

2021-03-10