Mysql function doesnt work

Question

DROP FUNCTION IF EXISTS shorten;
delimiter $$
CREATE FUNCTION shorten(s VARCHAR(255), n INT)
 RETURNS VARCHAR(255)
 BEGIN
 IF ISNULL(s) THEN
 RETURN '';
 ELSE IF n<15 THEN
 RETURN LEFT(s, n);
 ELSE IF CHAR_LENGTH(s) <= n THEN
 RETURN s;
 ELSE
 RETURN CONCAT(LEFT(s, n-10), ' ... ', RIGHT(s, 5));
 END IF;
 END$$

The message that I get is:

#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 '' at line 14 >

Where is teh error..cause I am new to making function in mysql.

UPDATE :

It points to an error with some rounded question mark between those lines:

ELSE IF n <15 THEN RETURN LEFT( s, n ) ;
ELSE IF CHAR_LENGTH( s ) <= n THEN RETURN s;

BTW, I use phpmyadmin

mysql version is: mysqlnd 5.0.10

• tadman: Have you tried evaluating a shorter version of this to see if you can narrow down where the problem is? There doesn't seem to be a > in this version.
• murgatroid99: I think the problem might be that you are calling delimiter $$ and then continuing to use ; as a delimiter.
• Jocelyn: @murgatroid99 this is exactly the way to do it, when declaring a procedure, function or trigger.
• feeela: @murgatroid99 No, that is correct when inserting functions. See: dev.mysql.com/doc/refman/5.0/en/create-procedure.html
• murgatroid99: OK. It's been a while since I've done SQL, so I don't really remember

Answer 1

There is an extra END IF you need to remove, and replace ELSE IF by ELSEIF:

DROP FUNCTION IF EXISTS shorten;
delimiter $$
CREATE FUNCTION shorten(s VARCHAR(255), n INT) RETURNS VARCHAR(255)
BEGIN
 IF ISNULL(s) THEN
 RETURN '';
 ELSEIF n<15 THEN
 RETURN LEFT(s, n);
 ELSEIF CHAR_LENGTH(s) <= n THEN
 RETURN s;
 ELSE
 RETURN CONCAT(LEFT(s, n-10), ' ... ', RIGHT(s, 5));
 END IF;
END$$

The last error Dmitry had was because of an extra semi-colon:

WRONG code: delimiter $$; 
GOOD code: delimiter $$

• Dmitry Makovetskiyd: Now I get this with your code: #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 '' at line 13
• Jocelyn: yes, the error reported is on the first ELSE IF. I'll fix that
• Dmitry Makovetskiyd: see update.. still get an error: #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 '' at line 12
• Jocelyn: I updated the code, check again please! It works fine using phpmyadmin, I checked it.
• Dmitry Makovetskiyd: #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 '' at line 12. see update

Answer 2

I am not sure bt i don't understand why you close if (END IF;) two times..? This may be a probable problem in your code. Please check it.And ELSE IF should be without space i.e ELSEIF. The code should be:

DROP FUNCTION IF EXISTS shorten;
delimiter $$
CREATE FUNCTION shorten(s VARCHAR(255), n INT)
RETURNS VARCHAR(255)
 BEGIN
 IF ISNULL(s) THEN
 RETURN '';
 ELSEIF n<15 THEN
RETURN LEFT(s, n);
 ELSEIF CHAR_LENGTH(s) <= n THEN
 RETURN s;
 ELSE
 RETURN CONCAT(LEFT(s, n-10), ' ... ', RIGHT(s, 5));
 END IF;
 END;$$
 delimiter;

• Dmitry Makovetskiyd: #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 '' at line 13
• heretolearn: the delimiter was not closed properly. that may be a reason. I have updated the code. Could you please check it now.And let me know if it works
• Dmitry Makovetskiyd: Your SQL query has been executed successfully ( Query took 0.0003 sec ).. However I do have some error concerning the delimiter
• Dmitry Makovetskiyd: Why wasnt the delimiter closed properly.. why do you have to close it like this. delimiter;?
• heretolearn: The default delimiter in SQL is ';' symbol that defines end of the query. When we define a delimiter, it takes the code between the delimiters as single query. The last line ie 'delimiter;' tell that the delimiter end here. It is done as we do for any other loop like when we use if we end the code as 'END IF;'. you can read more about delimiters at this link buysql.com/mysql/…

Answer 3

The real MySQL syntax for IF THEN ELSE is

 IF THEN
 ELSEIF THEN
 ELSE
 END IF

Actually, you're using ELSE IF instead, replace it by ELSEIF, and it's going to work

Reference: http://dev.mysql.com/doc/refman/5.0/en/if.html

DROP FUNCTION IF EXISTS shorten;
delimiter $$
CREATE FUNCTION shorten(s VARCHAR(255), n INT)
RETURNS VARCHAR(255)
 BEGIN
 IF ISNULL(s) THEN
 RETURN '';
 ELSEIF n<15 THEN
RETURN LEFT(s, n);
 ELSEIF CHAR_LENGTH(s) <= n THEN
 RETURN s;
 ELSE
 RETURN CONCAT(LEFT(s, n-10), ' ... ', RIGHT(s, 5));
 END IF;
 END$$

• Dmitry Makovetskiyd: #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 '' at line 14
• Touki: This works on my machine. Running SELECT shorten("This is a test",3) I get the expected output: Thi

Answer 4

CREATE FUNCTION shorten(s VARCHAR(255), n INT)
RETURNS VARCHAR(255)
BEGIN
IF ISNULL(s) THEN 
RETURN '';
ELSEIF n<15 THEN
RETURN LEFT(s,n);
ELSE 
IF CHAR_LENGTH(s) <= n THEN
RETURN s;
ELSE
RETURN CONCAT(LEFT(s, n-10), '... ',RIGHT(s,5));
END IF;
END IF;
END$$ 

try including one more END IF in ur statement and this should work :)