Coalesce two fields with null values

[lxm30]

I have two fields and if field1 is empty, I want to use the value in field2. (i.e. I never want to use field2 unless field1 is empty).

I was trying to use a coalesce function but it doesn't work well with null values.

Do I have any options beyond using fillnull for field2 with a value of *, coalescing the two and then using rtrim to get rid of the star?

[martinpu · Accepted solution]

Try this

|eval field3=case(isNotNull(field1),field1,isNotNull(field2),field2,1=1, NULL)

should return field 3 as field1 if it isnt null,field2 if that isnt null and field1 is null and NULL if both are NULL

[lxm30]

This works great - thanks so much for the quick response!

[lxm30]

(By * I mean some value, not necessarily the character "*").