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

Lukasz Szozda · Answer 1 · 2020-11-17T18:16:57.720

1

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

edited Nov 17 '20 at 18:16

answered Nov 17 '20 at 18:11

Lukasz Szozda

162,964
23
234
275

Thanks. But i found the above solution better for me. I know about the possibilities in MySQL 8, however i am forced to use MySQL 5 for this – Malthe Nov 17 '20 at 18:23

score 1 · Accepted Answer · answered Nov 17 '20 at 18:14

1

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

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

answered Nov 17 '20 at 18:14

GMB

216,147
25
84
135

Yes of course. I don't know why i did not think of this. Thank you very much! – Malthe Nov 17 '20 at 18:22

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

2 Answers2