Coalesce function for spaces/blanks

Question

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.

Ian Bjorhovde · Answer 1 · 2018-12-30T15:07:48.123

2

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.

edited Dec 30 '18 at 15:07

answered Dec 27 '18 at 20:09

Ian Bjorhovde

10,916
1
28
25

I completely agree on the `PREFERRED_NAME` being nullable, unfortunately I don't have control over the schema so it is what it is. – Jeff B Dec 27 '18 at 20:11
For those interested in the future, here's some documentation on ["Creating a user-defined function"](https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/apsg/src/tpc/db2z_defineudf.html). – Jeff B Dec 27 '18 at 20:52
This wouldn't work if your name fields are defined as CHAR (since those are padded with spaces on the end). – bhamby Dec 28 '18 at 15:14
@bhamby Wow, not sure what I was thinking there. Corrected answer, thanks :-) – Ian Bjorhovde Dec 30 '18 at 15:08

score 2 · Accepted Answer · answered Dec 27 '18 at 20:17

2

One method would be:

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

answered Dec 27 '18 at 20:17

Gordon Linoff

1,242,037
58
646
786

1

The TRIM isn't really needed, DB2 will [pad the shorter side with spaces for comparison](https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_stringcomparisionsintro.html). – bhamby Dec 28 '18 at 15:17

Coalesce function for spaces/blanks

2 Answers2