combining fields

[Gav]

This might not be the right newsgroup because I'm using SQL Server...

Anyway in my query I am trying to combine fields i.e.

SELECT FirstName + ' ' + LastName AS FullName FROM Names

My problem is if one of the fields is null I get null returned... is there a
way I can ignore the field if it is null?

Thanks
Gav

 

[Brendan Reynolds]

Try SELECT ISNULL(FirstName, '') + ' ' + ISNULL(LastName, '') ... etc.

Note that this is the T-SQL ISNULL function, not the VBA IsNull function,
we're using here. See 'ISNULL' under 'Transact-SQL Reference' in SQL Server
Books Online for details.

 

[Michel Walsh]

Hi,


You can use COALESCE to replace the nulls, since + propagates the NULL
(and &, which does not propagates the NULL, is not usable for that purpose
in MS SQL Server).


SELECT COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '') AS FullName
FROM Names


Hoping it may help,
Vanderghast, Access MVP

 

[Gav]

thankyou both for your help its just what I needed

Regards
Gav