CODEWITHSUNDEEP
sqldb2ibm-midrange
ehh
ehh

Reputation: 3480

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

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

Upvotes: 5

Views: 6934

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You can use NULLIF():

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

Upvotes: 11

Charles
Charles

Reputation: 23793

No.

Just use a CASE expression, assuming value could be NULL or empty, check for NULL first.

case
  when value is NULL then 'M'
  when value = '' then 'M'
  else value
end as newValue

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

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];

Upvotes: 6

Related Questions