MySQL-创建用户定义函数(UDF)时遇到问题 [英] MySQL - Trouble with creating user defined function (UDF)

问题描述

我正在尝试创建此功能:

I'm trying to create this function:

CREATE FUNCTION remove_non_alphanum (prm_strInput varchar(3000))
RETURNS VARCHAR(3000)
DETERMINISTIC

BEGIN

  DECLARE i INT DEFAULT 1;
  DECLARE v_char VARCHAR(1);
  DECLARE v_parseStr VARCHAR(3000) DEFAULT '';

  WHILE (i <= LENGTH(prm_strInput) )  DO
    SET v_char = SUBSTR(prm_strInput,i,1);

    IF v_char REGEXP '^[A-Za-z0-9]$' THEN
      SET v_parseStr = CONCAT(v_parseStr,v_char);  
    END IF;

    SET i = i + 1;
  END WHILE;

  RETURN trim(v_parseStr);
END

但是MySQL说:

13:52:45 [创建-0行，0.000秒] [错误代码:1064，SQL状态:42000]您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第5行的''附近使用

13:52:45 [CREATE - 0 row(s), 0.000 secs] [Error Code: 1064, SQL State: 42000] 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 5

我怎么可能做错了?语法对我来说似乎正确.

What could I being wrong? The syntax looks correct to me.

推荐答案

我找到了答案我发现这是一些奇怪的DB Visualizer问题.

I turns out it was some weird DB Visualizer issue.

在-/"和"/"中包含完整的代码对我有用:

Enclosing the complete block in "--/" and "/" worked for me:

--/
CREATE FUNCTION remove_non_alphanum (prm_strInput varchar(3000))
RETURNS VARCHAR(3000)
DETERMINISTIC
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE v_char VARCHAR(1);
  DECLARE v_parseStr VARCHAR(3000) DEFAULT '';
WHILE (i <= LENGTH(prm_strInput) )  DO
  SET v_char = SUBSTR(prm_strInput,i,1);
  IF v_char REGEXP '^[A-Za-z0-9]$' THEN
        SET v_parseStr = CONCAT(v_parseStr,v_char);  
  END IF;
  SET i = i + 1;
END WHILE;
RETURN trim(v_parseStr);
END
/

这篇关于MySQL-创建用户定义函数(UDF)时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！