SETP
asked on
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
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
Patrick Matthews
SELECT COALESCE(Title, '') + ' ' + COALESCE(Name, '') + ' ' + COALESCE(Surname, '') AS Client FROM Table1
SELECT ISNULL(Title,'') + ' ' + ISNULL(Name,'') + ' ' + ISNULL(Surname,'') AS Client FROM Table1
use isnull method it will work for u
SELECT isnull(Title, '') + ' ' + isnull(Name, '') + ' ' + isnull(Surname, '') AS Client FROM Table1
SELECT isnull(Title, '') + ' ' + isnull(Name, '') + ' ' + isnull(Surname, '') AS Client FROM Table1
Those will leave a leading space. Try this:
SELECT isnull(Title + ' ', '') + isnull(Name + ' ', '') + isnull(Surname + ' ', '') AS Client FROM Table1
SELECT isnull(Title + ' ', '') + isnull(Name + ' ', '') + isnull(Surname + ' ', '') AS Client FROM Table1
ASKER
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'
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'
ASKER
More specifically, I'm connecting to an Access database from within a VB.NET 2003 application...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What about the leading space?
Point taken, Ray. Plus forgot to use ampersand instead of +:
SELECT Nz(Title & ? ?, "") & Nz(Name & ? ?, "") & Nz(Surname, "") AS Client FROM Table1
SELECT Nz(Title & ? ?, "") & Nz(Name & ? ?, "") & Nz(Surname, "") AS Client FROM Table1