Of course you get NULL, NULL is a state meaning UNKNOWN - something
that can be very different from an empty string. Hence, you can never
know the result when NULL is involved in any computation and the
result must be NULL.

The best for you to do, is to always store the correct value in your
fields. If you know that the field has no value, then store an empty
string and don't leave it as NULL.

The second best could be to use the userdefined function SNVL, which
lets you specify what value the field should be interpreted as if it
is NULL. You have to declare this function before you can use it.

HTH,
Set

--- In firebird-support@yahoogroups.com, Lourenço de Paula <lourenco.
d@c...> wrote:

> Hi
>
> I'm trying to concat three string fields, see:
>
> SITUATION 1:
> FIELD1 FIELD2 FIELD3
> ------ ------ ------
> CONTENT1 CONTENT2 CONTENT3
>
> Than I execute the select:
>
> select FIELD1 || ' ' || FIELD2 || ' ' || FIELD3 from MYTABLE
>
> and it returns "CONTENT1 CONTENT2 CONTENT3", but when one of the

three fields is null the result is NULL, see:

>
> SITUATION 2:
> FIELD1 FIELD2 FIELD3
> ------ ------ ------
> CONTENT1 CONTENT2 NULL
>
> Than I execute the same select command:
>
> select FIELD1 || ' ' || FIELD2 || ' ' || FIELD3 from MYTABLE
>
> and it returns NULL instead of "CONTENT1 CONTENT2 ".
>
> What I need to do to get this result??
>
> Any help will be usefull