mysql - m #1064 - 你的 SQL 语法有错误我是 SQL 的新手

Question

我正在尝试从 (PhpMyAdimin) 运行此 CREATE 函数代码

---

CREATE FUNCTION  `CAP_FIRST`(input VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE len INT;
    DECLARE i INT;
    DECLARE charnum INT;
    declare SortedName varchar(255);

    SET len   = CHAR_LENGTH(input);
    SET input = LOWER(input);
    SET i = 1;
    set charnum = 1;
    set SortedName = '';


    WHILE (i <= len) DO
        if charnum = 1 then
            set SortedName = concat(SortedName,upper(mid(input,i,1)));
            set charnum = charnum + 1;
        else
            if mid(input,i,1) = ' ' then
                set SortedName = concat(SortedName,' ');
                set charnum = 1;
            else
                set SortedName = concat(SortedName,mid(input,i,1));
                set charnum = charnum + 1;
            end if;

        end if;


        SET i = i + 1;
    END WHILE;

    RETURN SortedName;
END

---

我总是得到这个

#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 4

Answer 1

您可能只需要 DELIMITER 语句，您可以使用 elseif，我建议将 mid() 替换为 substr() 或 substring():

DELIMITER $$

CREATE FUNCTION  `CAP_FIRST`(input VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE len INT;
    DECLARE i INT;
    DECLARE charnum INT;
    declare SortedName varchar(255);

    SET len   = CHAR_LENGTH(input);
    SET input = LOWER(input);
    SET i = 1;
    set charnum = 1;
    set SortedName = '';

    WHILE (i <= len) DO
        if charnum = 1 then
            set SortedName = concat(SortedName, upper(substr(input,i,1)));
            set charnum = charnum + 1;
        elseif mid(input,i,1) = ' ' then
            set SortedName = concat(SortedName,' ');
            set charnum = 1;
        else
            set SortedName = concat(SortedName, substr(input,i,1));
            set charnum = charnum + 1;
        end if;
        SET i = i + 1;
    END WHILE;

    RETURN SortedName;
END;
$$
DELIMITER ;