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

Question

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

Answer 1

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

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

Answer 2

You could use LATERAL:

SELECT IF(s.col = '', 'string not found', s.col)
FROM tab
,LATERAL (SELECT complex_expr AS col) s  -- LATERAL could be chained

db<>fiddle demo

But this only in MySQL 8.0+. Alternative is a suqbquery:

SELECT IF(s.col = '', 'string not found', s.col)
FROM (SELECT complex_expr AS col
      FROM tab) s