Re: Text concat problem

At 05:47 PM 11/8/00 -0600, Luis Magaña 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@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.