Function cannot call String Function LOCATE

[Rajarshi Biswas]

Hi !

I want to call MySQL string function LOCATE inside a function .
but when i try to execute then it says the string function LOCATE doesnt exist .

The error is as follows.

mysql> select GET_TRUNC(122344.535345345,'9999.99');//
ERROR 1305 (42000): FUNCTION <databasename>.LOCATE does not exist

i have also tried to run it dynamically , but is giving the same error.

set @fmt=format;
SET @query=" select LOCATE ('.',@fmt) into @pos " ;
PREPARE stmt from @query;
EXECUTE stmt;

The code is as follows

DELIMITER //
DROP function IF EXISTS GET_TRUNC //
create function GET_TRUNC( value INT , format VARCHAR (200))
returns INT
BEGIN
DECLARE position INT DEFAULT 0;
DECLARE ret INT;

set position = LOCATE ('.',format);

if position != 0 then
set ret = TRUNCATE(value,LENGTH(format)-position);
else
set ret =TRUNCATE(value,0);
end if;

return ret ;
END;
//


Please suggest how to resolve this .

Thanks in Advance
Rajarshi