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

  • Thread starter WWGD
  • Start date
In summary: Potentially disastrous in a multiuser environment, or single user if you forget to turn it back on again.
  • #1
WWGD
Science Advisor
Gold Member
6,934
10,341
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.
 
Technology news on Phys.org
  • #2
WWGD said:
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
 
  • Like
Likes WWGD
  • #3
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
 
  • Like
Likes Silicon Waffle
  • #4
Depending on your SQL dialect, something like
Code:
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
  2. 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).
  3. 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.
 
  • Like
Likes Silicon Waffle
  • #5
How about this ?

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT EmpFirst + ' ' + EmpMidI + ' ' + Emplast AS [fullname]
 
  • #6
Silicon Waffle said:
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.
 
  • Like
Likes Silicon Waffle

1. What is a null value in a database?

A null value in a database refers to a missing or unknown value. It represents a lack of data or a placeholder for data that is not yet available.

2. Why would I need to include nulls in my query?

Including nulls in a query allows you to retrieve data even if there are missing values. This can be helpful when analyzing large datasets where not all data points may be available.

3. How do I include nulls in my SQL query?

To include nulls in your SQL query, you can use the IS NULL or IS NOT NULL operators to filter out any null values. You can also use the COALESCE function to replace null values with a default value.

4. Can including nulls affect the results of my query?

Yes, including nulls in your query can affect the results. For example, if you are performing calculations, null values may be excluded from the calculation, resulting in a different outcome.

5. Are there any best practices for including nulls in a query?

One best practice is to use proper filtering to ensure that null values are only included when necessary. It is also important to understand how null values can affect your data and to handle them accordingly in your analysis.

Similar threads

  • Programming and Computer Science
Replies
7
Views
275
  • Programming and Computer Science
2
Replies
51
Views
3K
  • Programming and Computer Science
Replies
9
Views
1K
  • Programming and Computer Science
Replies
1
Views
829
  • Programming and Computer Science
Replies
2
Views
1K
  • Programming and Computer Science
Replies
6
Views
1K
  • Programming and Computer Science
Replies
7
Views
3K
  • Programming and Computer Science
Replies
9
Views
1K
  • Engineering and Comp Sci Homework Help
Replies
1
Views
1K
  • Computing and Technology
Replies
5
Views
2K
Back
Top