Subject Re: String Concatenation with null field
Author Adam
--- In, Fabrício Fadel Kammer
<ffkammer@c...> wrote:
> Hi all,
> I'm with a new problem with concatenation now!
> If I try to concatene two string fields and one of them is NULL the
> concatenation result is NULL :-(

That makes perfect sense.

If I have the string 'Hello', and I have another unknown or undefined
string that I append to the end of 'Hello', what is the result?
Obviously it is not known or not defined either.

NULL is a state, not a value. NULL is not synonymous with an empty
string or a 0 value.

> How can I prevent this? I need to create some sql scripts to
> some fields and I'm not able do know if the field is NULL or not.

If the field contains a NULL, then you can use the coalesce function
(FB 1.5 or higher). This function substitutes a value if a null is

Let me guess, someone doesn't have a middle name

select Coalesce(FirstName || ' ', '') || Coalesce(MiddleName
|| ' ', '') || Coalesce(LastName, '') as FullName
from employee

Yes this makes the SQL longer, but thinking that NULL = '' is a good
way to confuse the heck out of yourself.

Now the official version