NULLIF('','') Not really NULL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

NULLIF('','') Not really NULL?

Author

Topic

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2003-05-13 : 16:11:39

Ok, my 2nd weird one in two days. Jasper found the Knowledge Base Article for my weird UDF post yesterday, maybe he'll find this one as well, since I couldn't find anything. I checked the archives here as well.

SELECT NULLIF('',''),  ISNULL(NULLIF('',''),'Hello')

If the first column is NULL, why isn't the 2nd column "Hello"? At first I thought it maybe had something to do with the ANSI_NULLS setting, but it doesn't.

SQL Server 2000, sp2.

3P

==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 16:18:03

NULLIF is just comparing 2 values and if different, returns the first. If they're the same it returns NULL:

BOL:


NULLIF returns the first expression if the two expressions are not equivalent.

If the expressions are equivalent, NULLIF returns a null value of 
the type of the first expression.

Now why would you use NULLIF?

Brett

8-)

Edited by - x002548 on 05/13/2003 16:18:40

Edited by - x002548 on 05/13/2003 16:19:13

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2003-05-13 : 16:24:10

quote:

NULLIF is just comparing 2 values and if different, returns the first. If they're the same it returns NULL:

Exactly, which is why in my example the second column should be returning Hello, not an empty string. I undersand what NULLIF is doing, but when used as the first argument in ISNULL as in my example, ISNULL should return "Hello".

quote:

Now why would you use NULLIF?

Um, because it's available?

3P

==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-13 : 16:28:06

Interesting, ThreePea -- but I think I have the answer.

It is returning a char(0) datatype -- not a varchar() or a char() big enough to print the word "hello". That's because when using ISNULL(), the function must decide what type to return based on your empty strings, it is decideding on a char with a length of 0.

Try this:

SELECT NULLIF('',''), ISNULL(NULLIF(' ',' '),'Hello') <- 1 space

Then try this:

SELECT NULLIF('',''), ISNULL(NULLIF(' ',' '),'Hello') <- 2 spaces

Finally, try this:

SELECT NULLIF('',''), ISNULL(NULLIF(' ',' '),'Hello') <- 5 spaces

Very interesting, though..... by the same token, look at this:

SELECT NULLIF('',''), ISNULL(NULLIF('a','a'),'Hello')

same deal .... interesting that SQl would choose to return a datatype of length 0! but it does make sense.

- Jeff

Edited by - jsmith8858 on 05/13/2003 16:28:56

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 16:41:47

quote:

the type of the first expression.

Why? doesn't '' translate to char(0)?

SELECT LEN('')

quote:

Um, because it's available?

The Mt Everest of all reasons!

Have you used solve a particular problem though?

Brett

8-)

EDT: Ooops missed that piece

quote:

It is returning a char(0) datatype

Edited by - x002548 on 05/13/2003 16:44:00

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2003-05-13 : 16:44:51

quote:

same deal .... interesting that SQl would choose to return a datatype of length 0! but it does make sense.

Dang, another attempt at finding an undiscovered bug thwarted!

But yes, it does make sense. Thanks Jeff.

3P

==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2003-05-13 : 16:57:08

quote:

Have you used solve a particular problem though?

No, I haven't used NULLIF yet myself. For whatever reason I'm sort of the "figure out why SQL Server is behaving weird" guy around here, and one of the developers brought me that example. I have a feeling he was using it and then encountered an issue when the data contained an empty string.

I just checked our Impact Analysis tool and it looks like the developers here are using it often in places like this:

WHERE PO.Operation_Key = ISNULL(NULLIF(@Part_Operation_Key,0),PO.Operation_Key)

If the input paramenter @Part_Operation_Key is 0, ie no value is passed in from the asp page, then all records are returned. However if a value is passed in than only the records that match are returned.

So far I haven't seen it used where a simple CASE, IF, or ISNULL would suffice.

3P

==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton

pmr
Starting Member

37 Posts

Posted - 2003-05-13 : 19:50:04

quote:

WHERE PO.Operation_Key = ISNULL(NULLIF(@Part_Operation_Key,0),PO.Operation_Key)

If the input paramenter @Part_Operation_Key is 0, ie no value is passed in from the asp page, then all records are returned. However if a value is passed in than only the records that match are returned.

So far I haven't seen it used where a simple CASE, IF, or ISNULL would suffice.

Sometimes you can simplify things for yourself by breaking the logic up into its separate parts:

WHERE IsNull(@Part_Operation_Key, 0) = 0 OR PO.Operation_Key = @Part_Operation_Key

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-14 : 09:33:39

quote:

For whatever reason I'm sort of the "figure out why SQL Server is behaving weird" guy around

Then you'll have a long road ahead of you....

DECLARE @x datetime
SELECT @x = 0
SELECT @x

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-14 : 09:58:04

Brett --

you find that weird? Perfectly expected. That's why Arnold's "date only" method works.

it just converting the value 0 into a datetime, which happens to be how '1900-01-01' is stored internally -- as the value 0. It has to be stored within SQL server as *something*!

Convert a datetime to an int and print it, see what you get.

DECLARE @x datetime
set @x = '1900-01-01'

select convert(int, @x)

If SQL DIDN'T do that, there'd be problems!

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-14 : 10:37:28

Well, using 0 as the basis for that calculation is arbitrary -- any date would do as long as the two occurrences are the same. I used to put '19000101' in explicitly, but it takes too long to type!

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-14 : 11:04:13

But 0 is not a date...

What other RDBMS allows 0 as a valid date?

Even Access, for god sakes doesn't allow it....

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-14 : 11:31:20

Access stores dates much the same way as SQL server.

paste this into Access:

Sub test()
Dim d As Date
d = 0
msgbox d
MsgBox Format(d, "mm/dd/yyyy")
End Sub

Also, create a table with a datetime field in it, and then insert a 0 into that field and see what happens.

Granted, it's a different "base date" but still the same thing happening.

Brett -- like I said, computers store ALL datatypes as numbers. Once that concept is grasps, you will find you have a much higher understanding in general of computers and programming.

There is obviously an implicit conversion going on, but again -- it is stated clearly that both SQL Server and VBA peform these conversions so it's not really a side effect. You just have to know what you are doing.

Haven't you seen me post this:

convert(datetime, convert(int, getdate()))

What is that doing? dissect it and check it out .

What does:

convert(datetime, 0)

return? Does that make more sense for you?

- Jeff

Edited by - jsmith8858 on 05/14/2003 11:32:37

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-14 : 11:44:57

To be fair, I don't think the implicit conversion from integer to datetime is well-documented in BOL, beyond the fact that it does convert implicitly on the CAST and CONVERT matrix.

quote:

convert(datetime, convert(int, getdate()))

However:
SELECT convert(datetime, convert(int, convert(datetime, '18990101 12:00')))

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2003-05-14 : 11:46:40

quote:

Haven't you seen me post this:

convert(datetime, convert(int, getdate()))

That's cool, Jeff. We've always done it like this:

CAST(CONVERT( VARCHAR(10), GETDATE(), 101 ) AS DATETIME)

Same result, but I like your method better.

3P

==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-14 : 11:57:57

Jeff,

Did you try and create a table in Access with datetime and try and enter 0?

I don't think you did.

And Please:

quote:

Brett -- like I said, computers store ALL datatypes as numbers. Once that concept is grasps, you will find you have a much higher understanding in general of computers and programming.

All computers store informations as bits...

[edit]remove knee jerk reaction to condescension[/edit]

Yes I know all about:

quote:

convert(datetime, convert(int, getdate()))

What is that doing? dissect it and check it out .

What does:

convert(datetime, 0)

I also asked what other RDBMS allows 0 to be inserted in to a datetime feild...haven't see a reponse to that?

Plus doesn't it break the definition of what a datatype is?

BOL:
In Microsoft® SQL Server™, each column, local variable, expression, and parameter has a related data type, which is an attribute that specifies the type of data (integer, character, money, and so on) that the object can hold.

And the type of data (0) is not the type of date.

Brett

8-)

Edited by - x002548 on 05/14/2003 11:59:19

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-14 : 12:09:42

in Access, create a table called Table1, with 1 field called "field", of type date/time.

run this:

INSERT INTO Table1 ( field )
SELECT 0 AS Expr1;

Also add 1, add 123, add -4, any other numbers you like.

Again, Brett -- do not confuse TYPING IN the value 0 and trying to have a datagrid accept it as a date. We are talking the ACTUAL VALUE 0 and whether or not it gets converted to a date. and it does/can because dates are stored as numbers.

Look at results.

Brett -- as you said, the data type of data(0) is not a date. OK, then, what is it?

A bit?
A byte?
A long?
A float?
A double?
A numeric(9,3)
A char(1)
A varchar(231) ?

Every single 1 of those things handles the value of 0. So which is it?

It is all of those things and none of those things. it is just a value sitting there until it is actually STORED in the database or in memory or in a data structure. at THAT point and at that point ALONE does it have a concept of a data type -- not when it is typed in a user or stored in a text script in the SQL query analyzer.

I apologize, I am not trying to be condescending. I hope this is making more sense. And try the Access thing yourself.

- Jeff

Edited by - jsmith8858 on 05/14/2003 12:11:33

Edited by - jsmith8858 on 05/14/2003 12:12:14

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-14 : 12:38:14

Yup, tried the insert and access accepts 0 (note here, all it represents is 12:00 AM, which different than SQL Server).

Also In Microsofts infinte wisdom. MS Access Help:

quote:

IsDate returns True if the expression is a date or can be converted to a valid date; otherwise, it returns False. In Microsoft Windows, the range of valid dates is January 1, 100 A.D. through December 31, 9999 A.D.; the ranges vary among operating systems.

--

You can't enter 0 through the grid, or a form. Which is what most "developers are going to do.

And other than Access what other RDBMS handles a 0 datatype?

Also, and let me clarify.

Are you saying that '0' is the same as 0 (and the same as '01/01/1900')?

Fine, I'll give you the storage point (it's all bits anyway)

But Why Should:

DECLARE @x char(1)
SELECT @x = 0
SELECT @x
GO

Work, when the same rule don't apply for:

DECLARE @y int
SELECT @y = 'A'
SELECT @y
GO

Hmmmm?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-14 : 12:48:45

quote:

You can't enter 0 through the grid, or a form. Which is what most "developers are going to do.

That has nothing to do with ANYTHING! that is my point. it is just a interface which takes some text and pops it into a field. whether or not that INTERFACE allows you to type in certain values has NOTHING to do with what can be converted to a valid value or what can be stored!

Even better than your example, try this, and look at the message you get:

declare @d datetime;

set @d= '0'
select @d

(note that is the number 0)

Now, the conversion DOESN'T work because it has to go from a STRING to a date -- a different type of implicit conversion.

Implicit conversion of datatypes can be a confusing thing. '0' converts to a number 0 very easily. hopefully you can see that. '1/1/2000' converts to a date very easily, again, I hope you can see that (depending on your date settings, of couse). The number 0 converts to a date or a string very easily as well, because anything converts to a string and DATES ARE STORED AS NUMBERS -- so there is technically NO conversion occuring anyway!

BUT ... converting the string '0' or 'A' to a date does NOT work because the alogorithm looks for a string that is in some valid datetime format, which is DIFFERENT than the conversion from numbers-to-dates.

I'm afraid I have to give up at this point ...

I understand that this can be very confusing, and it is even harder for me to explain. Hopefully some of this made a little sense.

- Jeff

Edited by - jsmith8858 on 05/14/2003 12:50:35

Edited by - jsmith8858 on 05/14/2003 12:51:59

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-14 : 12:54:12

quote:

note here, all it represents is 12:00 AM

Brett - again, you are confusing INTERFACES with DATA. format the data in the form of mm/dd/yyyy and see what you get ... it is a true date/time. Just VBA/Access is choosing to display it as just the time becuase that's the way the system stores "just time" values -- with that default base date.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-14 : 12:55:19

Jeff,

I appreciate all of your time and the confusion on my part.

It's just that this doesn't exist on other platforms.

You'll get a datatype conversion problem.

Thanks again.

PS Talk about thread theft...sheesh...sorry guys.

Brett

8-)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources