Re: Text concat problem

Luis Magaña wrote:
> 
> Hi:
> 
> Have this curious situation and would like some help from you:
> 
> Create an employee table:
> 
> CREATE  TABLE  employee(
>    id_employee SERIAL PRIMARY KEY,
>    sex CHAR(1) DEFAULT 'm' CHECK(sex = 'f' OR sex = 'm'),
>    start_date DATE NOT NULL,
>    charge VARCHAR(50) NOT NULL,
>    last_name VARCHAR(50),
>    first_name VARCHAR(50) NOT NULL,
>    title VARCHAR(10) NOT NULL
> );
> 
> then fill it with a few values:
> 
> 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.  Have any idea or suggestion on how to
workaroundthis situation.
 

Yup.. it's due to the null..  I believe that the coalesce function can
get you out of this...  Speaking of which, why isn't it called NVL()?


http://www.postgresql.org/users-lounge/docs/7.0/user/functions.htm


Try this (untested):

select coalesce(title, ''::varchar) || ' ' || coalesce(first_name,
''::varchar) || ' ' || coalesce(last_name, ''::varchar) as fullname from
employee;