Re: concenation of strings and null values

Nigel J. Andrews wrote:
> 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:

not really, this behavoir is only with null values because all other
values are casted to text. So null (or none, or undefined ...) in most
languages map to "" (Empty string) when concenated.
It may be however the current behavior is according to SQL9[29],
since 1+null is null too (is a case in aggregates, isn't it?)
but I dont know of a human readable reference of the SQL specs.

>
> '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,'');

This works ;) Thanks :-)