Problem with SQL Server "NULL" vs ANSI NULL (SQL Server 2000? 8.0

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dvenus - 09 Jul 2008 18:29 GMT

Hi!

I have a strange problem with a table in a SQL Server database we have. When
I query one of the columns in the table with a "IS NULL" where clause I get
no rows returned. It does not matter if I set ansi_nulls on or off the result
is the same. However if I use the query "select ascii(column) from table" the
result in Query Analyzer shows "NULL". Just doing a "select * from table" in
Query Analyzer shows the column as blank, and normally if it null it displays
"[NULL]". Lastly when I select this table using an ODBC link from Oracle the
column comes across with NULL values.

What makes this really strange is there is a primary key that includes this
column and the column displays as "NOT NULL".

What is going on here? I am completely stumped.

Has the table or database been changed from "SQL Server NULLS" to
"ANSI_NULLS"?

Any ideas, fixes, hints, tips, flames, directions to KB or other
documentation is gratefully appreciated!

Dave Venus

Reply to this Message

Russell Fields - 09 Jul 2008 18:56 GMT

Dave,

Could this explain what is happening? By implication, your troublesome
column is character, but I don't know that for sure.

SELECT ASCII('') AS ValueOfEmptyString, '' AS EmptyString, NULL as
NULLString

As you can see ASCII of nothing (the empty string) cannot return a value,
since it has nothing to convert.

RLF

> Hi!
>
[quoted text clipped - 27 lines]
>
> Dave Venus

Reply to this Message

Linchi Shea - 09 Jul 2008 18:57 GMT

What you described seems to make sense. I'm not sure why you felt it's strange.

So the column has an empty space. IS NULL returns no row because it is not
NULL. ascii(column) returns NULL because there is no left-most character.
SELECT * FROM table doesn't display NULL because the value is not NULL.

Linchi

> Hi!
>
[quoted text clipped - 19 lines]
>
> Dave Venus

Reply to this Message

dvenus - 09 Jul 2008 19:01 GMT

Hi!

Thanks for the quick reply!

Is there a way to dump all the characters in the column?

Thanks!

Dave Venus

Reply to this Message

Roy Harvey (SQL Server MVP) - 09 Jul 2008 19:08 GMT

This might give you something to work with.

SELECT ASCII(substring(name,1,1)),
ASCII(substring(name,2,1)),
ASCII(substring(name,3,1)),
ASCII(substring(name,4,1)),
ASCII(substring(name,5,1)),
ASCII(substring(name,6,1)),
ASCII(substring(name,7,1)),
ASCII(substring(name,8,1)),
ASCII(substring(name,9,1))
FROM sysobjects

Roy Harvey
Beacon Falls, CT

>Hi!
>
[quoted text clipped - 5 lines]
>
>Dave Venus

Reply to this Message

dvenus - 09 Jul 2008 19:32 GMT

Hi!

Again thanks for the quick reply!

To me it is getting stranger still.....

Here is the query I ran:

SELECT ASCII(substring(stores,1,1)) col1,
ASCII(substring(stores,2,1)) col2,
ASCII(substring(stores,3,1)) col3,
ASCII(substring(stores,4,1)) col4,
ASCII(substring(stores,5,1)) col5,
ASCII(substring(stores,6,1)) col6,
ASCII(substring(stores,7,1)) col7,
ASCII(substring(stores,8,1)) col8,
ASCII(substring(stores,51,1)) col51,
datalength(stores) length
FROM ald
where ascii(stores) is null

And here is what it returned for each row:

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 CO51 Length
------- ------- -------- ------- -------- -------- ------- --------
------- ---------
NULL NULL NULL NULL NULL NULL NULL NULL NULL 0

Now the column only has 50 characters (nvarchar(50)). So column 51 must
just mean there is no such character.

I really appreciate everyone's help with this!

Dave Venus

Reply to this Message

Roy Harvey (SQL Server MVP) - 09 Jul 2008 20:31 GMT

So you have an empty string stored in a column that does not allow
NULLs. An empty string is different than a NULL, at least in SQL. In
SQL the NULLability is an attribute of the column as a whole, and if
it is NULLable then whether it is NULL or not is an attribute of the
column in each individual row.

Roy Harvey
Beacon Falls, CT

>Hi!
>
[quoted text clipped - 30 lines]
>
>Dave Venus

Reply to this Message

David Portas - 09 Jul 2008 23:05 GMT

> Hi!
>
[quoted text clipped - 30 lines]
>
> Dave Venus

That is the expected result. ASCII() returns NULL if the string in question
is the zero-length string.

Signature

David Portas

Reply to this Message