This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.