mysqlsqlstringmysql5sql-null

MYSQL 5: Returning expression if statement is false, similar to NULLIF

I have a long complicated expression that refers to multiple tables. The expression returns a string, but i want to return a different string if the returned string was empty "".

With the IF function i have to write my expression twice like:

IF(*expression* = "", "string not found", *expression*)

I know that NULLIF has something close to what i want as:

NULLIF(*expression*, "")

would return my expression if it was not empty, but if it was empty, it would return NULL, rather than a costum string.

Is there any way to avoid duplicating the expression, while still being able to choose a custom return string instead of just null, since duplicating the expressions (i have several cases of this in my query) would make the view-quera virtually unreadable

Solution

  • If you are fine with converting null values as well, you can mix NULLIF() and COALESCE():

    COALESCE(NULLIF(expression, ''), 'string not found')