MYSQL function fails - Failed SQL: (1064)

Question

This is my Liquibase changeset:

<changeSet author="user" id="1712203200334-1">    
    <sqlFile path="config/liquibase/IsValidCoordinatesFormat.sql"/>    
</changeSet>

This is the config/liquibase/IsValidCoordinatesFormat.sql file:

CREATE FUNCTION IsValidCoordinatesFormat(coordString VARCHAR(255)) RETURNS BOOLEAN
DETERMINISTIC
BEGIN
  DECLARE isValid BOOLEAN;
  DECLARE regex VARCHAR(255);
  SET isValid = FALSE;

  SET regex = '^(-?\\d+(\\.\\d+)?);(-?\\d+(\\.\\d+)?)$';

  IF coordString REGEXP regex THEN
    SET isValid = TRUE;
  END IF;

  RETURN isValid;
END;

And this is the error i'm getting

Reason: liquibase.exception.DatabaseException: 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 12 [Failed SQL: (1064) CREATE FUNCTION IsValidCoordinatesFormat(coordString VARCHAR(255)) RETURNS BOOLEAN
2024-04-04 08:00:53 DETERMINISTIC
2024-04-04 08:00:53 BEGIN
2024-04-04 08:00:53   DECLARE isValid BOOLEAN;
2024-04-04 08:00:53   DECLARE regex VARCHAR(255);
2024-04-04 08:00:53   SET isValid = FALSE;
2024-04-04 08:00:53 
2024-04-04 08:00:53   SET regex = '^(-?\\d+(\\.\\d+)?);(-?\\d+(\\.\\d+)?)$';
2024-04-04 08:00:53 
2024-04-04 08:00:53   IF coordString REGEXP regex THEN
2024-04-04 08:00:53     SET isValid = TRUE;
2024-04-04 08:00:53   END IF]

The SQL syntax looks correct for me. What's causing the problem?

Answer 1

The most part of your code is excess. Use single-statement function code which does not need in delimiter change:

CREATE FUNCTION IsValidCoordinatesFormat(coordString VARCHAR(255)) 
RETURNS BOOLEAN
DETERMINISTIC
RETURN COALESCE(coordString REGEXP '^(-?\\d+(\\.\\d+)?);(-?\\d+(\\.\\d+)?)$', FALSE);

If you guarantee that coordString won't be NULL, or NULL as function output is safe for you in this case then you may remove COALESCE().