--- In firebird-support@yahoogroups.com, 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

concatene

> 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
encountered.

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
http://www.firebirdsql.org/manual/nullguide.html

Adam