Thread: Text concat Problem

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. 

Thank you.

--
Luis Magaña
Gnovus Networks & Software
www.gnovus.com
Tel. +52 (7) 4422425
joe666@gnovus.com

On Wed, 8 Nov 2000, Luis [UNKNOWN] 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.  Have any idea or suggestion on how to
workaroundthis situation. 

Yes, use coalesce(<fieldname>, '') instead of fieldname.
So,
select coalesce(title, '') || ' ' || coalesce(first_name, '')
 || ' ' || coalesce(last_name, '') as fullname from employee;

SQL defines concatenation of a string value and NULL to be NULL.