Case Statement for Column Concatenation

Fname + MI + LNAME when MI <> Null

[Argblat]

Hi all,

I have a table (sql server 2000) with 3 columns...FNAME, MI, and LNAME.

I would like to create a select statement that concatenates the three columns into one.

The first step is easy enough:

select FNAME + ' ' + LNAME as Name from NAMES

However, if I add the Middle Initial to this query it will nullify any column where MI is null (most of them) becuse you can't concatenate columns where any column contains a null...

I obviously need a conditional statement here, and from the research I've been doing it seems SQL Server 2000 uses the keyword CASE

Unfortnaley I have no experience with conditionals in SQL and I would appreciate some guidance if anyone knows how to do this

Thank you
-Mike

[aspdaddy]

You dont need CASE, case is bad , you have two better options

1) set the db option concat null yields null to false
2) use the isNull/Replace functions like below.

CREATE VIEW dbo.FullName
AS
SELECT Replace(FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName, ' ', ' ') AS FullName
FROM  dbo.tblNames

[Argblat]

Got it:

select FNAME + ' ' + isnull(MI + ' ','') + LNAME as Name
from EMPLOYEE

Thank you AspDaddy

-Mike