SQL Query to join several fields even when some are empty

I have a query that returns the concatenation of several fields, such as:

SELECT Title + ' ' + Name + ' ' + Surname AS Client FROM Table1

The problem is that if any of the fields are blank, for example if the Title is blank, then the query does not return a value at all. Is it possible to alter the query so it returns whatever is available? So, for example, if only the name and the surname are filled in, then it would return "John Smith" for example, even though Title is null? Thanks
SETPAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
SETP said:
>>More specifically, I'm connecting to an Access database from within a VB.NET 2003
>>application...

It would be useful for you to state stuff like that up front next time :)  The syntax you
used in your question looked like SQL Server.

Try:
SELECT Nz(Title, "") + " " + Nz(Name, "") + " " + Nz(Surname, "") AS Client FROM Table1



0
 
Patrick MatthewsCommented:
SELECT COALESCE(Title, '') + ' ' + COALESCE(Name, '') + ' ' + COALESCE(Surname, '') AS Client FROM Table1
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT ISNULL(Title,'') + ' ' + ISNULL(Name,'') + ' ' + ISNULL(Surname,'') AS Client FROM Table1
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
kiran_ramisettiCommented:
use isnull method it will work for u
SELECT isnull(Title, '') + ' ' + isnull(Name, '') + ' ' + isnull(Surname, '') AS Client FROM Table1
0
 
GRayLCommented:
Those  will leave a leading space.  Try this:


SELECT isnull(Title + ' ', '') + isnull(Name + ' ', '') + isnull(Surname + ' ', '') AS Client FROM Table1
0
 
SETPAuthor Commented:
Thanks for all your replies. I fear that I forgot to mention that I am using an Access database, not a SQL Server database.
The COALESCE function doesn't seem to work at all - I get this error message: Undefined function 'COALESCE' in expression.
ISNULL gives the this error message: "Wrong number of arguments used with function in query expression 'ISNULL(Title,'') + ' ' + ISNULL(Name,'') + ' ' + ISNULL(Surname,'') AS Client FROM Table1'
0
 
SETPAuthor Commented:
More specifically, I'm connecting to an Access database from within a VB.NET 2003 application...
0
 
GRayLCommented:
What about the leading space?  
0
 
Patrick MatthewsCommented:
Point taken, Ray.  Plus forgot to use ampersand instead of +:

SELECT Nz(Title & ? ?, "") & Nz(Name & ? ?, "") & Nz(Surname, "") AS Client FROM Table1
0
All Courses

From novice to tech pro — start learning today.