Eval can not concatenate fields where there is a null value

[Rob]

Given that:

Field1="foo"

Field2=""

(Field2 has a null value)

and we use eval to concatenate the two

|eval Field3=Field1.Field2

or

|eval Field3=Field1+Field2

Then Field3 will contain the null value instead "foo". Instead it seems that with a null value we see it overwrite or ignore the non-null values and the whole thing just becomes a null value.

[Rob · Accepted solution]

The workaround for this is to use

|eval Field3=if(isnotnull(Field2), Field1.Field2, Field1)

This would assume that we know that Field1 will always have a value and sometimes Field2 might only contain a null value by using the if() and isnotnull() functions of eval to test whether the field has a NULL value. If there is no NULL value then we concatenate the fields, if there is a NULL value, we simply take the first field that has a value as the value we want to keep.

[Rob]

Thanks for that dwaddle! I like it, its sort of temporarily replacing the null value with an empty value and concatenating for a new field.

[dwaddle]

Hi Rob, another option is coalesce

| eval Field3=coalesce(Field1,"").coalesce(Field2,"")