Is there a similar function to COALESCE for empty string in AS400

Question

SELECT COALESCE(value, 'M') 
  FROM MyTable

In case the value is null the returned value will be set to "M"

Is there a similar function as COALESCE in case value is an empty string?

IBM i Version: 7.1

Answer 1

You can use NULLIF():

SELECT COALESCE(NULLIF(value, ''), 'M')
FROM [My Table]

Answer 2

The DB2 database appears to support COALESCE. But in any case, COALESCE would only work for replacing NULL, not empty string. One option here would be to just use a CASE expression:

SELECT CASE WHEN value <> '' THEN value ELSE 'M' END AS value
FROM [My Table];

If you want to handle both NULL and empty string together, then use this:

SELECT CASE WHEN COALESCE(value, '') <> '' THEN value ELSE 'M' END AS value
FROM [My Table];