Null and concat

[Ivan Smahin]

Hello Thorsten,

Friday, October 5, 2007, 3:38:05 PM, you wrote:

> when I do

> select concat(name,'-',firstname,'-',company) from visitors where  
> city like '%burg%'

> and there is a row with name = null and firstname = null I would  
> expect reading the wiki, that I'll get "NULL" as result, but I get "--
> objectmanufactur" now while this is fine in many situations to handle
> NULL as an empty string in string operations, the wiki tells  
> something different.

Could you send me that db?
I tried to reproduce it - but it works as expected.

--
CREATE TABLE "t1" (
        "f1" STRING (20) ,
        "f2" STRING (20) ,
        "m1" STRING (20) METHOD ('concat( f1, f2 )') );

INSERT INTO "t1" ("f1", "f2", "m1") VALUES ('abc', 'def', 'abcdef');
INSERT INTO "t1" ("f1", "f2", "m1") VALUES (NULL, NULL, NULL);
INSERT INTO "t1" ("f1", "f2", "m1") VALUES ('abc', NULL, NULL);
INSERT INTO "t1" ("f1", "f2", "m1") VALUES (NULL, 'def', NULL);
--


--
select concat( f1, '-', f2 ) from t1
------
abc-def
NULL
NULL
NULL

--
select m1 from t1
-----
abcdef
NULL
NULL
NULL

        
> Furthermore I then expect that NULL are always handled as empty  
> string in any string operation.

Null means indeterminacy.


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