Newb - SQL Server IIF() Function

Question

Newb - SQL Server IIF() Function

FGJ

SSC Enthusiast

Points: 179
More actions
February 25, 2021 at 10:56 pm

#3850311

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!

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

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

kaj SSCommitted Points: 1605 More actions · Answer 1

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.

FGJ SSC Enthusiast Points: 179 More actions · Answer 2

That's terrific, thanks.

That saved me a lot of time! LOL

Much appreciated!

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

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.

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

FGJ SSC Enthusiast Points: 179 More actions · Answer 4

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

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

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.

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

FGJ SSC Enthusiast Points: 179 More actions · Answer 6

Sorry... I was multitasking. LOL.

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

Just curious. 🙂

FGJ SSC Enthusiast Points: 179 More actions · Answer 7

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..... 🙂

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

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

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 9

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.

FGJ SSC Enthusiast Points: 179 More actions · Answer 10

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!

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 11

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.

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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 12

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

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 13

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.

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

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 14

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

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

I think much better to use CASE

SELECTCASE 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