SQL statement problem while filling a dataset in vb.net?

Question

I am filling a data set in VS 2005. My database is Microsoft SQL Server 2005. I have a table and want to combine 2 fields into on under alias Name. Below is my SQL statement

select FIRM_NAME + FULL as [NAME] from ATTORNEYS

the problem is that it will return a lot of null valuse if one of the fields is null. When I was in Access could fix this by adding "&" in place of the +. Now that im in SQL and the field types are varchar it wont let me use the "&" i get the error

The data types varchar and varchar are incompatible in the boolean AND operator

How can i get around. See either they are giving full name or a Firm Name never both so insted of having 2 fields i just want to combine the fields into one under an alias and have it always have a value either the Firms Name or the Full Name or the employee. What can I use instead of the "&" that will do the samething in SQL as "&" does in Access

Answer 1

Try this:

SELECT ISNULL(FIRM_NAME,'') + ISNULL(FULL,'') FROM ATTORNEYS