Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Including Nulls in Query.

  1. Dec 21, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  2. jcsd
  3. Dec 21, 2015 #2

    Borg

    User Avatar
    Gold Member

    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
     
  4. Dec 22, 2015 #3

    harborsparrow

    User Avatar
    Gold Member

    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
     
  5. Dec 22, 2015 #4
    Depending on your SQL dialect, something like
    Code (Text):
    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.
     
  6. Dec 22, 2015 #5
    How about this ?

    SET CONCAT_NULL_YIELDS_NULL OFF
    SELECT EmpFirst + ' ' + EmpMidI + ' ' + Emplast AS [fullname]
     
  7. Dec 22, 2015 #6
    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.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Including Nulls in Query.
  1. NULL undeclared (Replies: 1)

  2. I need a SQL query (Replies: 3)

  3. Need Help on SQL query (Replies: 3)

Loading...