Create MySQL function statement

Question

I've written a function but it gives me mistake a the second line (create statement) if anyone could help me, I really appreciate:

CREATE FUNCTION GetPrefix (phone_num VARCHAR(30)) RETURNS varchar(30)

deterministic
BEGIN

DECLARE x  INT;
DECLARE prefix varchar(30);
SET x = 0;

for prefix in SELECT code
    FROM tab_len
while (length(phone_num)) > 0
    do
    if prefix<>left(phone_num, length(phone_num)-x)
        then  set x=x+1  ;
    else return 1 ;
END while ;

END $$;

and I receive this error :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'for prefix in SELECT code FROM tab_len while (length(phone_n' at line 9

Well, maybe you shouldn't try to come up with your own syntax, but try to find the correct syntax in the manual. And your code makes absolutely no sense. Maybe you should rather describe, what you're trying to do first — fancyPants
– fancyPants, Commented Apr 21, 2015 at 14:24

Kaushik · Accepted Answer · 2015-04-21 15:26:22Z

2

DELIMITER $$
DROP FUNCTION IF EXISTS GetPrefix $$
CREATE FUNCTION GetPrefix 
(
phone_num VARCHAR(30)
) 
RETURNS varchar(30)

BEGIN

DECLARE var_x  INT DEFAULT 0;
DECLARE var_prefix VARCHAR(100);
SET phone_num = IFNULL(phone_num,'');

-- your logic will go here.
return phone_num;


END$$ 
DELIMITER ;

SELECT GetPrefix('test');

This is right syntax to write a function in mysql. check out the differences. Take a look Here

edited Apr 21, 2015 at 15:26

answered Apr 21, 2015 at 14:36

Kaushik

2,0901 gold badge24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create MySQL function statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related