MySQL Workbench 6.2 create function

Question

I'm trying to create a function in MySQL workbench, I have this SQL code:

DELIMITER $$
CREATE FUNCTION  `regex_replace` (pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN 
 DECLARE temp VARCHAR(1000); 
 DECLARE ch VARCHAR(1); 
 DECLARE i INT;
 SET i = 1;
 SET temp = '';
 IF original REGEXP pattern THEN 
  loop_label: LOOP 
   IF i>CHAR_LENGTH(original) THEN
    LEAVE loop_label;  
   END IF;
   SET ch = SUBSTRING(original,i,1);
   IF NOT ch REGEXP pattern THEN
    SET temp = CONCAT(temp,ch);
   ELSE
    SET temp = CONCAT(temp,replacement);
   END IF;
   SET i=i+1;
  END LOOP;
 ELSE
  SET temp = original;
 END IF;
 RETURN temp;
END;
$$
DELIMITER ;

When I put this in phpMyAdmin, my function is created without any problems. When I click on "Add Routine" in MySQL Workbench and then paste this code, I get this error: Syntax error: 'DELIMITER' (identifier) is not valid input at this position

I'm no expert on MySQL functions, so I don't really know what to do with this. What is the problem here? I really want to fix this, because MySQL Workbench now shows my function as _SYNTAX_ERROR instead of regex_replace.

score -1 · Answer 1 · edited May 23 '17 at 12:26

-1

When using the builtin procedure editor, MySQL Workbench adds a few extra commands:

USE `test`; // <----------
DROP procedure IF EXISTS `p2`;  // <----------

DELIMITER $$
USE `test`$$ // <----------
CREATE PROCEDURE test.`p2` ()
LANGUAGE SQL
DETERMINISTIC
COMMENT 'Adds "nson" to first and last names in the record.'
BEGIN
SELECT 'Hello World';
END $$

DELIMITER ; // <----------

If you apply the above slashes to your procedure, it should help :)

For reference: Stored Procedures Using MySQL Workbench

edited May 23 '17 at 12:26

Community

1
1

answered Feb 10 '15 at 09:07

Bobby DeVeaux

150
5

This is for a procedure, how do I use this in a function? When I put `USE `test`; // <---------- DROP procedure IF EXISTS `p2`; // <----------` on the top (and change test to regex_replace or something else). MySQL Workbench gives my the following error: `Syntax error: 'USE' (use) is not a valid input at this position` – Feb 10 '15 at 09:19

MySQL Workbench 6.2 create function

1 Answers1