Mysql Error Delimiter: Error at Line Delimiter

Question

I am trying to use a mysql function that I have found but I seem to be getting some errors with this. Here is the function:

SET GLOBAL log_bin_trust_function_creators=1; 
DROP FUNCTION IF EXISTS digits; 
DELIMITER $$
CREATE FUNCTION digits( str CHAR(32) ) RETURNS CHAR(32) 
BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret CHAR(32) DEFAULT ''; 
  DECLARE c CHAR(1); 
  SET len = CHAR_LENGTH( str ); 
  REPEAT 
    BEGIN 
      SET c = MID( str, i, 1 ); 
      IF c BETWEEN '0' AND '9' THEN  
        SET ret=CONCAT(ret,c); 
      END IF; 
      SET i = i + 1; 
    END ; 
  UNTIL i > len END REPEAT; 
  RETURN ret; 
END 
$$
DELIMITER ; 

SELECT digits('123ab45cde6789fg');

I am using Mariadb 5.5

Server version: 5.5.50-MariaDB MariaDB Server

Here is the error that I see:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '$$
DELIMITER ; 

I have tried a few suggestions such as changing the delimiter which was originally a pipe. Moving the delimiter from next to END to underneath it which I have done, however, the error seems to occur in both.

I have tried basic modifications to fix the error but it still occurs, it seems though when removing this section I no longer get any errors, but the result is not correct.

REPEAT 
  BEGIN 
    SET c = MID( str, i, 1 ); 
    IF c BETWEEN '0' AND '9' THEN  
      SET ret=CONCAT(ret,c); 
    END IF; 
    SET i = i + 1; 
  END ; 
UNTIL i > len END REPEAT; 

Any pointers would be great.

Update

I have tested a few other versions and have the same issue. I am using Navicat currently.

Server version: 5.5.50-MariaDB MariaDB Server
Server version: 5.5.31-0ubuntu0.12.10.1 (Ubuntu)
Server version: 5.1.73 Source distribution
Server version: 5.5.52-MariaDB MariaDB Server

Answer 1

I can't reproduce the problem:

MariaDB [_]> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 5.5.56-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [_]> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DROP FUNCTION IF EXISTS digits;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DELIMITER $$

MariaDB [_]> CREATE FUNCTION digits( str CHAR(32) )
    -> RETURNS CHAR(32)
    -> BEGIN
    ->   DECLARE i, len SMALLINT DEFAULT 1;
    ->   DECLARE ret CHAR(32) DEFAULT '';
    ->   DECLARE c CHAR(1);
    ->   SET len = CHAR_LENGTH( str );
    ->   REPEAT
    ->     BEGIN
    ->       SET c = MID( str, i, 1 );
    ->       IF c BETWEEN '0' AND '9' THEN
    ->         SET ret=CONCAT(ret,c);
    ->       END IF;
    ->       SET i = i + 1;
    ->     END;
    ->   UNTIL i > len END REPEAT;
    ->   RETURN ret;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DELIMITER ; 

MariaDB [_]> SELECT digits('123ab45cde6789fg');
+----------------------------+
| digits('123ab45cde6789fg') |
+----------------------------+
| 123456789                  |
+----------------------------+
1 row in set (0.00 sec)