MySQL, IFNULL(), COALESCE() on String not replacing

Question

My statement should replace every empty title_column with 'no name', but it doesn't:

SELECT COALESCE(main_table.title_column, 'no name') AS title
FROM main_table;

IFNULL() behaves the same way.

What am I doing wrong ?

Croesus

Could it be you're seeing empty string values instead of null values? – Machinate

obviously, thats the case. and another user posted the solution... – Croesus

Answer 1

COALESCE and IFNULL substitute only NULL values, your table seem to contain empty strings:

SELECT
  COALESCE(NULLIF(main_table.title_column, ''), 'no name') AS title
FROM main_table;

Gaye

Answer 2

In MySQL a NULL string and an empty ('') string are not the same thing.

You have a few options, for consistency I use CASE...

CASE WHEN main_table.title_column = '' THEN 'no name' ELSE main_table.title_column END

Other options could be the COALESCE(NULLIF()) shown on another answer (which uses NULLIF() to turn empty strings into NULLs and then uses coalesce as you wanted to).

Or possibly just IIF() to shorten the CASE statement...

IIF(main_table.title_column = '', 'no name', main_table.title_column)

Fahey

Answer 3

If you prefer a functional way of giving solutions you can always use this:

DELIMITER $$
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
-- Note: add your server side comments to remember what this routine does on your Api
-- --------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS `IfNullOrEmpty`$$
CREATE FUNCTION `IfNullOrEmpty`(s TEXT, value TEXT) RETURNS TEXT
NOT DETERMINISTIC
READS SQL DATA
BEGIN

IF ( (s is null) OR (trim(s) = '')  ) THEN
    return value;
END IF;
return s;    
END$$

Usage:

SELECT IfNullOrEmpty(t.FieldName,'No Name Given') as Name FROM cms_Table t

Consistence

Answer 4

used this solution for MySql,

SELECT IF((TRIM(main_table.title_column) = ''),'no name', COALESCE(main_table.title_column, 'no name')) AS title FROM main_table

Euraeurasia