mysqlcreate-function

trouble shoot a creating a mySQL function for parsing a string


I'm getting a SYNTAX error attempting to create a mySQL function to parse a string. It's probably something simple that I'm not realizing given that I have more experience creating FN's with SQL then MySQL.

Here's my function:

CREATE FUNCTION fn_parse_string (string1 varchar(2048),datapoint1 varchar(20),delimiter1 

varchar(1)) 
RETURNS varchar(60) DETERMINISTIC

BEGIN
    DECLARE datareturn1 VARCHAR(60)
    
    IF INSTR(string1, datapoint1) > 0 
        THEN 
            SET datareturn1 = SUBSTRING_INDEX(SUBSTRING(string1,
                                                        INSTR(string1, datapoint1) + LENGTH(datapoint1),
                                                        LENGTH(string1)),
                                                delimiter1,
                                                1)
    ELSEIF IFNULL(datapoint1,'') = '' 
        THEN 
            SET datareturn1 = 'Blank Field'
    ELSE SET datareturn1 = 'N/A'
    END IF;


RETURN (datareturn1);
END

Solution

  • It seems that some semicolons missing, try it in this way please:

    CREATE FUNCTION fn_parse_string (string1 varchar(2048),datapoint1 varchar(20),delimiter1 
    
    varchar(1)) 
    RETURNS varchar(60) DETERMINISTIC
    
    BEGIN
        DECLARE datareturn1 VARCHAR(60);
        
        IF INSTR(string1, datapoint1) > 0 
            THEN 
                SET datareturn1 = SUBSTRING_INDEX(SUBSTRING(string1,
                                                            INSTR(string1, datapoint1) + LENGTH(datapoint1),
                                                            LENGTH(string1)),
                                                    delimiter,
                                                    1);
        ELSEIF IFNULL(datapoint1,'') = '' 
            THEN 
                SET datareturn1 = 'Blank Field';
    
        ELSE SET datareturn1 = 'N/A';
        END IF;
    
    
    RETURN (datareturn1);
    END