MySQL, the equivalent of coulece for empty values?

Question

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

Example:

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

Answer 1

Use the ANSI CASE statement / expression :

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

In SQL, there is no logical or MySQL. MySQL actually stores the value as INT, the values ​​are zero or one:

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

Answer 2

You can make NULL from an empty string in MySQL:

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

Answer 3

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

One possible solution would be to pass a string with a set of values ​​separated by a separator using the CONCAT_WS function (see the CONCAT_WS () function ). You will also need to define a line separator if your separator is included in one of the set values.

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 you run 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 line separator so that there are no conflicts with your set values.

Answer 4

This worked for me:

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