CODEWITHSUNDEEP
mysqlfunction
Melane
Melane

Reputation: 1

Create MySQL function statement

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

Upvotes: 0

Views: 67

Answers (1)

Kaushik
Kaushik

Reputation: 2090

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

Upvotes: 2

Related Questions