concatenation and null values

M

Mike D

I want to concenate the following 3 fields..

first, mi, last

I want to return a blank for a field being a null. Not sql return null for
an empty value.

select first + ' ' + mi + '. ' + last
returns just the records where there is something in the fields. and returns
a blank record if there is nothing in the field
if I add ( mi is null or mi is not null) this doesnt help either.

I need it to look like the following

jane a. doe
john doe
b. doe
james b.

I need all records but concatenated....??
 
Top