Re: Text concat problem

At 05:47 PM 11/8/00 -0600, Luis =?UNKNOWN?Q?Maga=F1a?= wrote:

>insert into employee(title,first_name,start_date,charge) values('Mr.
X','Smith',date(now()),'None');
>insert into employee(title,first_name,start_date,charge) values('Mr.
Y','Smith',date(now()),'None');
>insert into employee(title,first_name,start_date,charge) values('Mr.
Z','Smith',date(now()),'None');
>
>so far there is no problem at all, the problem comes here:
>
>select title || ' ' || first_name || ' ' || last_name as fullname from
employee;
>
> fullname
>----------------
>
>
>
> (3 rows)
>
>Doesn't work !!!!, I'm thinking it is because of the null value in last_name.

Right. NULL means "has no value", it's not the empty string. The result of
concatenating with NULL is NULL.

> Have any idea or suggestion on how to workaround this situation.

It's a classic NULL issue. 1+NULL = NULL, too, for instance. Try
something like
"default ''" in your table definition rather than use null. Then you'll be
using
the empty string for concatenation. 'abc' || NULL = NULL. 'abc' || '' =
'abc'
which appears to be what you want.

This is standard SQL92 behavior...

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.