SQL: Only put a period "." if there is a middle intial.

[PrgrmsAll]

I am trying to concatenate four fields into one. I have the following data:
Last Name
First Name
Middle Initial (can be null)
Degree (M.D., D.O., etc.)

I need my resultant field data to look like the following:
"Simpson, Bart A., M.D."

I'm having a problem writing SQL that is sensitive to placing the period after the middle initial only if there is a middle initial present. If there isn't a middle initial, I just want the comma: "Simpson, Bart, M.D.".

This is probably a simple code. We're all learning!

Thanks in advance.

 

[swampBoogie]

select LastName + ', ' + FirstName 
 case when middleInitial is not null then 
    ' ' + middleInitial + '.' 
 else '' end + ', ' + Degree 
 from t

 

[PrgrmsAll]

Perfect, swampboogie! thank you!

 

[JabbaTheNut]

I have the same problem trying to create a View. However, when I try the above suggestion in the Query Designer, I get an error that says the CASE statement is not supported in the Query Designer.

I have used ISNULL(MiddleInitial, '') before. However, it does not handle the '.' issue stipulated above. Is there a way to do the above in a View? What function would I use that can be used in the Query Designer.

Game Over, Man!

 

[SQLSister]

Simple, stop trying to create a view in query designer. Use query analyzer instead. There are lots of things which are legal t-SQL commnads that Query deigner does not support.

 

[JohnDTampaBay]

This should accomplish the same goal. Assuming ANSI_NULLS is ON, if middleInitial is NULL the concatenation of the space and period resolves to NULL, so ISNULL substitutes empty string. Good luck!

SELECT LastName + ', ' + FirstName 
   + ISNULL(' ' + middleInitial + '.', '')
   + ', ' + Degree 
FROM t

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[JabbaTheNut]

AngelWPB,

That worked perfectly! Thanks

I wonder why the CASE statement (and others like it) is supported in the Query Analyzer, but not in the Query Designer? Seems strange to me.

Game Over, Man!