CODEWITHSUNDEEP
sqldb2
Jeff B
Jeff B

Reputation: 8982

Coalesce function for spaces/blanks

Is there something similar to the coalesce function for working with a CHAR column that may be blank?

For example, let's say I want to use PREFERRED_NAME if it exists, but use FIRST_NAME if it doesn't and the table I'm working with has data like this?

+-------------+----------------+ 
| FIRST_NAME  | PREFERRED_NAME | 
+-------------+----------------+ 
| JOHN        |                | 
| STEPHANIE   |                | 
| STEPHEN     | STEVE          | 
+-------------+----------------+

I was hoping to be able to write something like this:

SELECT COALESCE(PREFERRED_NAME,FIRST_NAME)
FROM   COMPANY.EMPLOYEES

... but it looks like COALESCE only works for NULL since for the data above my results look like this:

+---------+
| Column1 |
+---------+
|         |
|         |
| STEVE   |
+---------+

I found a potential solution in a question for MySQL that looks like this:

SELECT COALESCE(NULLIF(PREFERRED_NAME,''),FIRST_NAME)

Is using NULLIF or writing a CASE statement the best solution at this point? I was hoping for a single function that'd do it for me.

Upvotes: 0

Views: 3651

Answers (2)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11042

You could use a CASE statement:

case when length(trim(preferred_name)) > 0 
     then preferred_name 
     else first_name 
     end

You could certainly use this logic (or the COALESCE(NULLIF(...)) logic in a user-defined function (UDF) that would allow you to "use a single function."

A better solution would be to make the PREFERRED_NAME column nullable, and avoid having 0-length values, but this is likely a more complicated solution.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270021

One method would be:

SELECT COALESCE(NULLIF(TRIM(E.PREFERRED_NAME), ''), E.FIRST_NAME)
FROM COMPANY.EMPLOYEES E

Upvotes: 2

Related Questions