MySQL, coalesce equivalent for empty values?

I know that coalesce will return the first non-null value that is passed to it. is there something simlilar that will return the first not empty / not false value?

example:

select FunctionIWant(0,'','banana') as fruit;  //returns banana.

Solution 1:

You could make NULL from empty string in MySQL :

SELECT coalesce(NULLIF(email, ''), '[email protected]') FROM users WHERE id=1000000;

Solution 2:

Use the ANSI CASE statement/expression:

SELECT CASE 
         WHEN LENGTH(col) = 0 OR col IS NULL THEN 'banana'
         ELSE col
       END AS fruit

There's no boolean in SQL, or MySQL. MySQL actually stores the value as an INT, values zero or one:

SELECT CASE 
         WHEN col = 0 THEN 'banana'
         ELSE col
       END AS fruit

Solution 3:

There is no such function in MySQL, but you can develop your own stored function. The main difficulty here is that the number of parameters to pass to the function can vary.

One possible solution could be to pass a string with a set of values separated by a separator by using the CONCAT_WS function (see CONCAT_WS() function). You would also have to define a string delimiter in case your separator would be included in one of the values of the set.

Here is an example script:

DELIMITER |;
CREATE FUNCTION MY_COALESCE (
     p_set_string TEXT
    ,p_delimiter CHAR(1)
  ) RETURNS TEXT
    DETERMINISTIC
BEGIN

    RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(
              REPLACE(REPLACE(
                      CONCAT(p_delimiter,p_set_string,p_delimiter)
                     ,CONCAT(p_delimiter,'0',p_delimiter),'')
                 ,CONCAT(p_delimiter,p_delimiter),'')
             ,p_delimiter,2),p_delimiter,-1)
    ;
END;
|;
DELIMITER ;

SET @separator=',', @delimiter='$';
SELECT
  MY_COALESCE(
      CONCAT_WS(CONCAT(@delimiter,@separator,@delimiter),0,'','banana')
     ,@delimiter
    ) as fruit
;

When running the previous script you get the following output:

MySQL> --------------
MySQL> SET @separator=',', @delimiter='$'
MySQL> --------------
MySQL> 
MySQL> Query OK, 0 rows affected (0.00 sec)
MySQL> 
MySQL> --------------
MySQL> SELECT
MySQL>   MY_COALESCE(
MySQL>       CONCAT_WS(CONCAT(@delimiter,@separator,@delimiter),0,'','banana')
MySQL>      ,@delimiter
MySQL>     ) as fruit
MySQL> --------------
MySQL> 
MySQL> +--------+
MySQL> | fruit  |
MySQL> +--------+
MySQL> | banana |
MySQL> +--------+
MySQL> 1 row in set (0.02 sec)

You can of course adapt the value of the string delimiter so there won't be any conflicts with your set values.

Solution 4:

This worked for me:

SELECT IF(myValue > 0, myValue, 'empty string') AS Value FROM myTable;