Checking Null in MySQL Nested If

Question

I am trying to write a MySQL function that pulls a value out of a text field that is formatted as a JSON dict. However, for some reason I can't get the nested IF statement to work without syntax errors.

Here is the function:

DROP FUNCTION IF EXISTS json_key;
DELIMITER $$

CREATE FUNCTION `json_key`(`col` TEXT, `key` VARCHAR(255)) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE ret VARCHAR(255);
    DECLARE pos INT;
    IF (col IS NULL) THEN SET ret = "";
    ELSEIF (col = "") THEN SET ret =  "";
    ELSEIF (LOCATE(key, col) = 0) THEN SET ret =  "";
    ELSE 
        SET pos := LOCATE(key, col);
        SET len := LENGTH(key);
        SET bg  := pos + len;
        SET ret = SUBSTR(col, bg, LOCATE('"', col, bg) - bg));
    END IF;
    RETURN ret;
END $$
DELIMITER ;

Here is the error I am getting:

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 'key, col) = 0) THEN SET ret =  "";

ELSE 
SET pos := LOCATE(key, col);
SET len :=' at line 7

Any idea on what I'm missing?

Ravinder Reddy · Accepted Answer · 2014-03-31 17:44:25Z

1

key is a reserved word.

Either use a different name, say jKey or use backticks (`) to get it accepted

Change these lines:

ELSEIF (LOCATE(key, col) = 0) THEN SET ret =  "";
ELSE 
    SET pos := LOCATE(key, col);
    SET len := LENGTH(key);

To:

ELSEIF (LOCATE(`key`, col) = 0) THEN SET ret =  "";
ELSE 
    SET pos := LOCATE(`key`, col);
    SET len := LENGTH(`key`);

Refer to: MySQL Reserved Words

answered Mar 31, 2014 at 17:44

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

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

Comments

Rahul · Accepted Answer · 2014-03-31 17:45:08Z

1

Remove the parenthesis around the condition and check; like

ELSEIF LOCATE(key, col) = 0 THEN SET ret =  "";

You can as well change your condition checking like

IF (col IS NULL OR  col = "" OR LOCATE(key, col) = 0) THEN SET ret = "";
ELSE 
    SET pos := LOCATE(key, col);
    SET len := LENGTH(key);
    SET bg  := pos + len;
    SET ret = SUBSTR(col, bg, LOCATE('"', col, bg) - bg));
END IF;

edited Mar 31, 2014 at 17:45

answered Mar 31, 2014 at 17:19

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

BringMyCakeBack Over a year ago

Ravinder caught the error, but I voted up this one as well because it cleans up the overall structure.

Hackerman · Accepted Answer · 2014-03-31 17:18:41Z

0

Try this:

SET len := (coalesce(LENGTH(key),0));

answered Mar 31, 2014 at 17:18

Hackerman

12.3k2 gold badges37 silver badges47 bronze badges

Collectives™ on Stack Overflow

Checking Null in MySQL Nested If

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related