Syntax error in MySQL function using WHILE

Question

I am trying to write a function for MySQL that will strip all non-digit characters from a submitted phone number. I keep getting a syntax error.

DELIMITER $$
CREATE FUNCTION cleanPhone(phone VARCHAR(20)) RETURNS VARCHAR(20)
  DETERMINISTIC 
BEGIN
  DECLARE clean VARCHAR(20) DEFAULT '';
  DECLARE idx TINYINT DEFAULT 1;
  DECLARE ch CHAR(1);
  DECLARE digits CHAR(10) DEFAULT '0123456789';

  WHILE idx <= LENGTH(phone) DO
    SET ch = MID(phone,idx,1);
    IF LOCATE(ch, digits) > 0 THEN SET clean = CONCAT(clean, ch);
    SET idx = idx + 1;
  END WHILE;
  RETURN clean;
END;
$$
DELIMITER ;

When I try to execute this code, I get the following error:

ERROR 1064 (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 '
WHILE;
  RETURN clean;
END
' at line 13

Sardor Dushamov · Accepted Answer · 2016-03-11 04:57:47Z

1

You missed END IF;

  WHILE idx <= LENGTH(phone) DO
    SET ch = MID(phone,idx,1);
    IF LOCATE(ch, digits) > 0 THEN SET clean = CONCAT(clean, ch);
    END IF;
    SET idx = idx + 1;
  END WHILE;

answered Mar 11, 2016 at 4:57

Sardor Dushamov

1,6674 gold badges17 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vipin Jain · Accepted Answer · 2016-03-11 04:59:59Z

0

You forget to close if block.

DELIMITER $$
CREATE FUNCTION cleanPhone(phone VARCHAR(20)) RETURNS VARCHAR(20)
  DETERMINISTIC 
BEGIN
  DECLARE clean VARCHAR(20) DEFAULT '';
  DECLARE idx TINYINT DEFAULT 1;
  DECLARE ch CHAR(1);
  DECLARE digits CHAR(10) DEFAULT '0123456789';

  WHILE idx <= LENGTH(phone) DO
    SET ch = MID(phone,idx,1);
    IF LOCATE(ch, digits) > 0 THEN SET clean = CONCAT(clean, ch);
      SET idx = idx + 1;
    END IF;
  END WHILE;
  RETURN clean;
END $$
DELIMITER ;

answered Mar 11, 2016 at 4:59

Vipin Jain

3,77618 silver badges36 bronze badges

Collectives™ on Stack Overflow

Syntax error in MySQL function using WHILE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related