would some one tell me what is wrong with this function
Iget this error when I apply: Operation failed: There was an error while applying the SQL script to the database. ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FUNCTION `fn_number2words`(_Number double(18,2)) RETURNS varchar(8000) ' at line 1
----------------------------------------------------------------------------
This is the Function
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
-------------------------------------------------------------------------------------
Also, is there a better function that u guys know about that convert numbers to words ? Thanks..
Answer Answered by Ian Gilfillan in this comment.
Make sure you understand what log_bin_trust_function_creators does and that you're happy setting it. Alternatively, read up on what DETERMINISTIC, NO SQL and READS SQL DATA do and use one of them in your definition.