What is the best way to handle null middle initials in a SQL query?

[WWGD]

Hi all,
I have a table Emp ( EmpFName, EmpMidI, Emplast) ,

where EmpMidI is the middle name initial, which may be null. I want to get the full name in a single
record, which I would do, if there were no nulls, as :

Select EmpFirst + ' ' + EmpMidI + ' ' + Emplast AS [fullname]

How do I rewrite this query to allow for null middle initials?
Thanks.

 

[Borg]

Hi all,
I have a table Emp ( EmpFName, EmpMidI, Emplast) ,

where EmpMidI is the middle name initial, which may be null. I want to get the full name in a single
record, which I would do, if there were no nulls, as :

Select EmpFirst + ' ' + EmpMidI + ' ' + Emplast AS [fullname]

How do I rewrite this query to allow for null middle initials?
Thanks.

You probably want to use something like the coalesce function to change a null value to something else.
http://www.w3schools.com/sql/sql_isnull.asp

 

[harborsparrow]

You could just write an IF ELSE statement like you would in any programming language.

IF EmpMidI IS NOT NULL
BEGIN
--include EmpMidl
END
ELSE
BEGIN
--omit EmpMidl
END

 

[pbuk]

Depending on your SQL dialect, something like

SELECT EmpFirst + IIF(ISNULL(EmpMidI), '', ' ' + EmpMidI) + ' ' + Emplast AS [fullname]

but note:

1. You have called the first column EmpFName in the table definition and EmpFirst in the query
   [*]This is probably because you have managed to use 3 different naming conventions for your 3 columns - this should get an award for unmaintainable code!* Pick one and stick to it: I would probably choose FirstName, MiddleInitials, LastName (note that there may be more than one middle initial and prefixing column names with the table name is not in general a good habit).
   [*]I can't think of a situation where you would want to do this in SQL - this kind of manipulation is best left to the client application.
   

* I know this is only an example, but you need to work at it until these things become instinctive.

 

[Silicon Waffle]

How about this ?

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT EmpFirst + ' ' + EmpMidI + ' ' + Emplast AS [fullname]

 

[pbuk]

How about this ?

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT EmpFirst + ' ' + EmpMidI + ' ' + Emplast AS [fullname]

1. MS specific.
2. Even MS have deprecated it ("In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error").
   
3. Potentially disastrous in a multiuser environment, or single user if you forget to turn it back on again.