CAP_FIRST function gives syntax error

Question

I found this function for capitalizing the first letter of words in a string, but it is giving me a syntax error on line 8 when I try to run it in SQL in PHPMyadmin. Can anyone help me sort out what the problem is? Here is the code:

CREATE FUNCTION CAP_FIRST (input VARCHAR(255))

RETURNS VARCHAR(255)

DETERMINISTIC

BEGIN
DECLARE len INT;
DECLARE i INT;

SET len   = CHAR_LENGTH(input);
SET input = LOWER(input);
SET i = 0;

WHILE (i < len) DO
    IF (MID(input,i,1) = ' ' OR i = 0) THEN
        IF (i < len) THEN
            SET input = CONCAT(
                LEFT(input,i),
                UPPER(MID(input,i + 1,1)),
                RIGHT(input,len - i - 1)
            );
        END IF;
    END IF;
    SET i = i + 1;
END WHILE;

RETURN input;
END;

And this is the error: MySQL said: Documentation #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 8

Answer 1

The delimiter signals the DB engine the end of your statement. Normally it is ;. But that would end the stored procedure at the first ;. And its definition would be incomplete.

You can change the delimiter and add it to the end of your procedure. After that change the delimiter back to ;

delimiter |
CREATE FUNCTION CAP_FIRST (input VARCHAR(255))
...
END;
|
delimiter ;