Re: Is this a feature ?

On Tuesday 04 June 2002 04:18, David BOURIAUD wrote:
> Hi the list !
> I've read the docs, and found that you could concatenate two or more
> strings in one select statement like this :
> select
>     civ || name || forname as identity
> from
>     person;
>
> This just works fine and returns rows with only one column named identity.
> So far, if one of the fields (civ, name or forname) is null, identity is
> null... That doesn't seems right, since it means that 1+2+0 = 0 !!!!
> Has anybody else seen the same strange behaviour ? And is there a way to
> work this around ?
> Please, don't answer me to use the function textcat (text, text), since it
> behaves the same and makes queries quite unreadeable...
> eg.
> select
>     textcat ( textcat (civ, name), forname) as identity
> from person;
> is quite readeable, but when you add fields separator, it isn't.

NULL != 0.  1+2+NULL=NULL.

Try ...
CASE WHEN civ IS NOT NULL THEN civ ELSE '' END ||
CASE WHEN name IS NOT NULL THEN name ELSE '' END ||
CASE WHEN forname IS NOT NULL THEN forname ELSE '' END AS identity

- James