sql conctante first name and last name

netJP12L

Hi guys I have two fields, FirstName and LastName. I am able to concatnate both names but if one of the name is null then I don't get the results. For example, First name is John and there is no last name then upon concatanting both names i get null. I have set the database "Concatenate Null yeilds Null" to True but still no success. Perhaps, could anyone of you help me. Thanks

i j russell

Have a look at COALESCE.

Avi Berger

netJP12L wrote:

First name is John and there is no last name then upon concatanting both names i get null.

netJP12L wrote:

I have set the database "Concatenate Null yeilds Null" to True

It sounds like this is doing exactly what you told it to. You could: 1) follow i.j.russell's suggestion and use COALESCE, [edit - added] 2) try changing the setting, 3) write code that tests for Null and handles the various cases, or 4) don't allow Null values in these fields. Looking through the list, i.j.russell's suggestion sounds very good to me.

Please do not read this signature.

Corporal Agarn

For MS SQL Server I use ISNULL like ISNULL(FirstName + ' ', '') + ISNULL(LastName, '') I put a space after the first name but if it is null then the space does not get added.