Convertir numbers en palabras en el resultado MYSQL! Usando Query

Estoy intentando imprimir un resultado de la siguiente manera cuando ejecuto una consulta en mysql.

Name Salary Sal_in_Words Mohan 45000 Rupees Forty Five Thousand Only 

La columna Salario tiene un valor de 45000 y en la tercera columna el Valor en el segundo colunm se convierte en palabras a través de Consulta.

Encontré un artículo donde en Oracle podemos get el resultado anterior utilizando la siguiente consulta:

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

SALIDA:

 Name Salary Sal_in_Words Suresh 45000 Rupees Forty Five Thousand Only 

En MySQL probé LPAD, RPAD pero eso simplemente agrega cadenas al resultado que no convierte palabras a cadena.

Encontré algunos tutoriales pero todos los explicados sobre "TO_CHAR (dates)".

¿Hay alguna forma de hacerlo?

 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) + "Hundnetworking" + 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 
 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 _hundnetworkings 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, _hundnetworkings, _tens, _ones; /*SELECT _inputNumber, _chunk, _tensones, _hundnetworkings, _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 hundnetworkings IF _hundnetworkings <> '0' THEN SELECT word INTO _numtmp1 FROM tbl_numbers WHERE number = CONCAT('0',_hundnetworkings); SET _outputString = CONCAT(_numtmp1, ' hundnetworking ', _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 

Tomé la respuesta de TomPHP y la extendí para trabajar hasta 6 dígitos:

SELECT number_into_words(999999); resultaría en nine hundnetworking ninety nine thousand nine hundnetworking ninety nine

También actualicé la configuration inicial de dig1 a dig6 para que fuera más legible para mí (entiendo que hay forms más eficientes de hacerlo).

 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 hundnetworkings, 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 hundnetworking'); when dig6=2 then set ans=concat(ans, 'two hundnetworking'); when dig6=3 then set ans=concat(ans, 'three hundnetworking'); when dig6=4 then set ans=concat(ans, 'four hundnetworking'); when dig6=5 then set ans=concat(ans, 'five hundnetworking'); when dig6=6 then set ans=concat(ans, 'six hundnetworking'); when dig6=7 then set ans=concat(ans, 'seven hundnetworking'); when dig6=8 then set ans=concat(ans, 'eight hundnetworking'); when dig6=9 then set ans=concat(ans, 'nine hundnetworking'); 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 hundnetworking'); when dig3=2 then set ans=concat(ans, 'two hundnetworking'); when dig3=3 then set ans=concat(ans, 'three hundnetworking'); when dig3=4 then set ans=concat(ans, 'four hundnetworking'); when dig3=5 then set ans=concat(ans, 'five hundnetworking'); when dig3=6 then set ans=concat(ans, 'six hundnetworking'); when dig3=7 then set ans=concat(ans, 'seven hundnetworking'); when dig3=8 then set ans=concat(ans, 'eight hundnetworking'); when dig3=9 then set ans=concat(ans, 'nine hundnetworking'); 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 $$ 

No hay una function simple en MySQL que necesita para escribir una function con la ayuda de la function que puede lograr este resultado.

Verifique lo siguiente … Funciona para mí .. Referencia

  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 hundnetworkings, 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 hundnetworking'); when dig3=2 then set ans=concat(ans, 'two hundnetworking'); when dig3=3 then set ans=concat(ans, 'three hundnetworking'); when dig3=4 then set ans=concat(ans, 'four hundnetworking'); when dig3=5 then set ans=concat(ans, 'five hundnetworking'); when dig3=6 then set ans=concat(ans, 'six hundnetworking'); when dig3=7 then set ans=concat(ans, 'seven hundnetworking'); when dig3=8 then set ans=concat(ans, 'eight hundnetworking'); when dig3=9 then set ans=concat(ans, 'nine hundnetworking'); 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 ; 

Si obtiene el siguiente 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) 

Ejecuta esta consulta:

 SET GLOBAL log_bin_trust_function_creators = 1; 

Después de eso, crea una function en Mysql:

Llame a la function Al igual que el siguiente command:

 SELECT number_to_string( 666 ); 

Obtendrá la salida como:

 number_to_string( 666 ) six hundnetworking sixty six 

Espero que esto ayude a otros.