I am getting an error when I try and run this. Here is the error:
Error Code: 1146. Table 'motest.tbl_numbers' doesn't exist
Ran this to fix one error:
SET GLOBAL log_bin_trust_function_creators = 1;
Testing function
select motest.FN_NUMBER2WORDS(23453);
fn_number2words Function
CREATE DEFINER=`malkurwi`@`%` FUNCTION `fn_number2words`(_Number double(18,2)) RETURNS varchar(8000) CHARSET latin1
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;