CODEWITHSUNDEEP
mysqlsqldatabase
mfayaz
mfayaz

Reputation: 3

How to remove #1064 - You have an error in your SQL syntax error in mysql

Whenever I try to to run this function I get the following error message:

#1064 - 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 'END' at line 10 

DROP FUNCTION IF EXISTS regNum;
DELIMITER $$
CREATE FUNCTION regNum(i INT)
RETURNS CHAR(5)
 BEGIN
    RETURN CONCAT(
     CHAR(i / 26000 % 26 + 65), 
     CHAR(i / 1000 % 26 + 65), 
     CHAR(i / 100 % 10 + 48),
     CHAR(i / 10 % 10 + 48), 
     CHAR(i % 10 + 48))
 END;
 $$
 DELIMITER ;

Upvotes: 0

Views: 493

Answers (1)

juergen d
juergen d

Reputation: 204766

Move the ; after end, before it.

Since you change the delimiter from ; to $$, the semicolon is not a delimiter any more (outside the function definition).

Just a special character and the DB engine does not know what to do with it and throws an error. You need to put it at the end of the return statement to end the statement inside the function.

DROP FUNCTION IF EXISTS regNum;
DELIMITER $$
CREATE FUNCTION regNum(i INT)
RETURNS CHAR(5)
 BEGIN
    RETURN CONCAT(
     CHAR(i / 26000 % 26 + 65), 
     CHAR(i / 1000 % 26 + 65), 
     CHAR(i / 100 % 10 + 48),
     CHAR(i / 10 % 10 + 48), 
     CHAR(i % 10 + 48));
 END
 $$
 DELIMITER ;

Upvotes: 2

Related Questions