Newb - SQL Server IIF() Function

  • I am using the following function:

    IIf([var1Title] != '', [var1Title] + ' - ' + [var1Topic] + ' - ' + [var1Name], '') AS Item1

    In some cases, the value of var1Name is NULL. Oddly, when var1Name is NULL, the value retuned for the entire line is NULL.

    I am hoping the value returned would be simply 'var1Title - var1Topic -'

    What am I missing here? 🙂

    Thanks all!

  • The standard behavior for concatenation of strings is that if one of the strings to be concatenated is a null the concatenated string will also be null.

    You can change this behavior in SSMS if you like. Go to Query --> Query Options --> Advanced, where you will see that SET CONCAT_NULL_YIELDS_NULL is ticked.

    Or you can execute SET CONCAT_NULL_YIELDS_NULL OFF, which will set the behavior off in your session.

  • That's terrific, thanks.

    That saved me a lot of time! LOL

    Much appreciated!

  • Gosh, no.  Don't change the SET CONCAT_NULL_YIELDS_NULL setting on your server or in your session!  Use the CONCAT function instead.  Please read the following...

    https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-ver15

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Oh... ok. I'll read the article.

     

    Can I simply add the SET CONCAT_NULL_YIELDS_NULL to the top of my view?

  • Yes, but I just recommended against doing that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sorry...  I was multitasking. LOL.

    Reading this now.... is the recommendation a performance issue or a security issue?

    Just curious. 🙂

  • Reading a little more about this - could you advise on the syntax for CONCAT, when using IIF?

    I am struggling getting this to work, using IIFs... see below....

    SELECT

    IIf([var1Title] != '', [var1Title] + ' - ' + [var1Topic] + ' - ' + [var1Name], '') AS Item1,

    IIf([var2Title] != '', [var2Title] + ' - ' + [var2Topic] + ' - ' + [var2Name], '') AS Item2

    From Table1

    Thanks again for the recommendation..... 🙂

  • Might be easier if you showed us a couple of examples of values for Var1Title, Var1Topic, and Var1Name... and what you expect the result to be.   🙂

     

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • IIf([var1Title] != '', [var1Title] + ' - ' + [var1Topic] + ' - ' + [var1Name], '') AS Item1

    In some cases, the value of var1Name is NULL. Oddly, when var1Name is NULL, the value retuned for the entire line is NULL.

    One of the principles of SQL is that "NULLs propagate." So any computation (math, dates, strings) with a NULL in it will return a null result. In a pure relational model, there's only one kind of NULL, but it SQL since the column has to have a data type. The result can also have a data type associated with it.

    You might also want to start writing SQL instead of some local dialect from the spreadsheet. The ANSI/ISO standards use <> and not != for inequality. The use of IIF is taken from spreadsheets; an SQL programmer would use CASE expressions. In ANSI/ISO standard SQL string concatenation is done with two pipes ||, but Microsoft hasn't come up to standard yet and has to fall back on the old Sybase + or call to a proprietary concatenation function. I assume you know that the square brackets are also proprietary dialect and would normally be double quote marks in ANSI/ISO standard SQL.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • jcelko212 32090 - Thanks, appreciate this. As mentioned, I am relatively new to SQL. I'll continue to read up...

    The Dixie Flatline - the result that I am hope for based on IIf([var1Title] != '', [var1Title] + ' - ' + [var1Topic] + ' - ' + [var1Name], '') AS Item1 is: If var1Title is not blank, then write [var1Title] - [var1Topic] -[var1Name]

    Currently, when var1Topic OR var1Name is null, the entire string is null.

    Thanks all!

  • FGJ wrote:

    jcelko212 32090 - Thanks, appreciate this. As mentioned, I am relatively new to SQL. I'll continue to read up...

    The Dixie Flatline - the result that I am hope for based on IIf([var1Title] != '', [var1Title] + ' - ' + [var1Topic] + ' - ' + [var1Name], '') AS Item1 is: If var1Title is not blank, then write [var1Title] - [var1Topic] -[var1Name]

    Currently, when var1Topic OR var1Name is null, the entire string is null.

    Thanks all!

    Yup... We know that.  Pretend that you want the information to be displayed... What would you want to be displayed (including any dashes) if either or both or NULL?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  •  

    IIF([var1Title] != '', [var1Title] + ISNULL(' - ' + [var1Topic], '') + ISNULL(' - ' + [var1Name], ''), '') AS Item1

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • This is why I asked the OP for what they expected if the output were to be displayed...

    IF the " - " that leads a NULL value are to NOT be included as Scott has portrayed with his code, then here's how to use CONCAT to do the same thing.

    IIF(var1Title != '', CONCAT(var1Title, ' - '+var1Topic, ' - '+var1Name), '') AS Item1

    If the dashes are to be included regardless of whether or not the variable elements are NULL, then just replace each "+" with a comma.  While that doesn't seem to make sense here, I have seen it many times where that's what the OP/User/Client wanted.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I dislike IIF ... its "familiar" for people that use similar functions in Excel, but I think CASE is better in SQL

    IIF only has two outcomes, true/false, and to get multiple tests the blessed things are nested and becomes write-only code

    SELECT	IIF(var1Title != '', var1Title + ' - ' + var1Topic + ' - ' + var1Name
    , IIF(var1Topic != '', var1Topic + ' - ' + var1Name
    , '')
    ) AS Item1

    I think much better to use CASE

    SELECT	CASE WHEN var1Title != '' THEN var1Title + ' - ' + var1Topic + ' - ' + var1Name
    WHEN var1Topic != '' THEN var1Topic + ' - ' + var1Name
    ELSE ''
    END AS Item1

    NOTE:

    CASE WHEN [var1Title] != '' THEN 'NotBlankString' ELSE 'NULL_or_BlankString' END

    Beware what happens when [var1Title]  IS NULL - in this case this is probably what you want - either BlankString OR NULL gives you a BlankString, and any "value" gives you Title, Topic, Name

    Also

    [var1Title] + ' - ' + [var1Topic] + ' - ' + [var1Name]

    will (as a whole) be NULL if any of the three is NULL

    CONCAT([var1Title] + ' - ', [var1Topic] + ' - ', [var1Name])
    or
    CONCAT([var1Title], ' - ', [var1Topic], ' - ', [var1Name])

    will output a BLANK for each of the components if they are NULL. The first will only also include the suffix ' - ' to elements which are NOT NULL (but will suffix it if the element is Blank String)

    The second will always suffix ' - ' whether the element is a value, a blank string, or NULL

Viewing 15 posts - 1 through 15 (of 62 total)

You must be logged in to reply to this topic. Login to reply