Left(null,5) <> 'anything' is not true?

Question

Left(null,5) <> 'anything' is not true?

puruzio

SSC Rookie

Points: 26
More actions
July 14, 2018 at 8:27 am

#373578

Why is it that "left(null,8) <> 'anything' " is not true with ansi_nulls set to off?
I'm trying to find a way to avoid having to use isnull() or coalesce() everywhere in my script in order to avoid counterintuitive null comparison results like this.
set ansi_nulls off
select 'true' where null <> 'anything' ----> This returns 'true'
select 'true' where left(null,8) = null ----> This returns 'true'
select 'true' where left(null,8) <> 'anything' ----> This returns nothing

Viewing 5 posts - 1 through 4 (of 4 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

Because, when comparing to a NULL (for example 1 = NULL) the return value is "unknown". Unknown is neither true or false, and hence doesn't meet the criteria. To compare to a NULL you have to use IS NULL or IS NOT NULL.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

frederico_fonseca SSCoach Points: 16157 More actions · Answer 2

Really you should not set it to off and should code accordingly.

But in either case it is doing what the manual says.
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-2017

set ansi_nulls off declare @var varchar(3) set @Var = left(null, 8)

select 'case1', 'true' where null <> 'anything' ----> This returns 'true' -- one of the operands is a literal null union all select 'case2', 'true' where left(null,8) = null ----> This returns 'true' -- one of the operands is a literal null union all select 'case3', 'true' where left(null,8) <> 'anything' ----> This returns nothing -- neither of the operands is a variable that is NULL or a literal NULL union all

select 'case4', 'true' where null <> 'anything' ----> This returns 'true' -- one of the operands is a literal null union all select 'case5', 'true' where @Var = null ----> This returns 'true' -- one of the operands is a literal null and the other is a null variable union all select 'case6', 'true' where @Var <> 'anything' ----> This returns 'true' -- one of the operands is a literal null and the other is a null variable

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

From Microsoft Docs

:exclamationmark: Important
In a future version of SQL Server, ANSI_NULLS will be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101105 More actions · Answer 4

set ansi_nulls off
I'm trying to find a way to avoid having to use isnull() or coalesce() everywhere in my script in order to avoid counterintuitive null comparison results like this.

Don't. Just don't.

Some people will override NULL with '' first thing in code / query to avoid to deal with NULLs in code, which can be difficult and frustrating. You may want to test out a workaround such as that.

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