Re: concenation of strings and null values

On Tue, 7 Oct 2003, Tino Wildenhain wrote:

> Hi,
>
> SELECT 'abc'::text || 'def'::text;
>
> returns 'abcdef' as we know.
>
> SELECT 'abc'::text || ''::text;
>
> returns 'abc'
>
> SELECT 'abc'::text || null::text;
>
> returns null
>
> The last example looks like a bug,
> but if it is intentionally so, its
> at least very annoying and inconvenient.

Looks correct to me. Sure, might be annoying but we'd rather have correctness I
think :)

What you're asking for the equivalent of is:

    something1 || ???? || something3 to equate to: something1something3

which you obviously can't say in the general case because you don't know if
???? really is an empty string or in fact something much more significant as in
the example:

    'do' || 'not' || 'concatinate'

Therefore the whole expression should evaluate to null otherwise you'd never
know you didn't actually know if you should or shouldn't concatinate.

>
> Can someone enlighten me if this is
> in accordance to some not so transparent
> rules of SQL92 or '99 and if so, how
> to work around this?

In this instance use coalesce() as in: SELECT 'abc' || coalesce(null,'');


--
Nigel J. Andrews