Search code examples
sqlsql-serversql-server-2000

Null column with null values

Background:

I'm dealing with a legacy SQL Server 2000 database (yes, I AM changing it!). It has a column, which we can call name, which is nvarchar(100) and not null. I've verified this.

When I run this query:

select name
from mytable
where name is null

I get no results. However, when I run this query:

select name
from mytable
where name = ''

I get about 100 results. I wanted to find out a bit more about these blank values, since spaces would be legal in a non null column, so I ran this query:

select ASCII(substring(name, 1, 1))
from s
where name = ''

This returns "32" for about a third of the results, i.e. a space. For the other 2/3rds however, it is returning null.

Question:

  1. How can there be null values in a column that has a not null definition?
  2. Why are these null values not appearing in a "name is null" query?
Solution

  • You don't have NULLs in the column name, you have verified that with your first query.

    The name = '' condition matches both empty strings and strings of spaces.

    When name is empty, SUBSTRING(name, 1, 1) is also empty, and so is the argument of ASCII. In this case ASCII returns NULL. When name is a string of spaces, the argument supplied to ASCII is a space, therefore the result is 32, the ASCII code of the space character.