CONCAT and NULLs

[Ivan Smahin]

Hello Ruslan,

Friday, May 30, 2008, 6:38:32 PM, you wrote:

> On 5/30/08 6:03 PM, "william humphrey" <shoreagent at gmail.com> wrote:

> Hi William,

>> Tell me when you concate using  || ' ' ||  in valentina it evaluates nulls as
>> string ( in other words when it is "a"  || ' ' ||  NULL  || ' ' ||  "b"  --
>> the result is ab but when I did NULL  || ' ' || 'a'  || ' ' ||  NULL  || ' '
>> ||  'b' the result was NULL in Oracle the result would be string but in MySQL
>> the result would always be NULL. If in Valentina the result is supposed to
>> always be NULL then maybe the field that I think is NULL actually isn't. Is
>> Valentina the same as MySQL for this or Oracle or something different?
>> 
>> I would like it to return string as evaluating to NULL just because there is
>> one NULL seems to me not a good feature.

> I will ask Ivan comment on this.

> But in general rule of SQL Standard is:

>     if AT LEAST one parameter of a function is NULL
>     then result also is NULL.

> Agree this is quite logical rule.


You can check is it null before concatenation.
See ifNull function. There is a common approach if you need some
"custom" result instead of just NULL.


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com