Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2010
    Posts
    22

    Unanswered: Blank entries are treated as zero in comparisons

    Hi there. SQL Server 2008 R2 user here. Been getting a lot of great help from the forums, so here comes another question. This one I think should be rather easy for the pros, I can't imagine the solution is anything huge.

    In Form A, a user inputs data. Upon submit, a row is inserted into a table. If the user left certain input fields blank, that's fine, the fields in the inserted row appear blank. For this example, let's say Field X takes a number. If a user inputs a number, great. If not, great. Doesn't matter at this point.

    In Form B, the user can perform a search to find all records where that column is greater than, less than, or equal to some number the user wants to search for. Everything about the program is working great, EXCEPT, it counts all of the blank entries as zero (even though when you query the table, there are no zeroes there, just blanks).

    So for example, if I say, "give me all records where Field X is less than 10," what I want is only the results that actually have data and are less than 10, not ALL results including blanks as zeroes. As it is now, the above search will give me everything that is blank through 10.

    ------

    I can think of an easy workaround, and that would be to scan the data in Form A before inserting it into the row. If that field is left blank, insert it into the table as like "x" or something, something that isn't a number and thus isn't included as a result when searching greater than, less than, or equal to a number. That would probably work. But I'd rather actually understand the nature of zeroes and blanks in SQL server rather than just work around and ignore the problem.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please can you confirm what you mean by "blank"? Is the data actually NULL? (NULL has a meaning in an RDBMS, "blank" does not).
    Can you also confirm that these columns are INT and definitely not a character data type (e.g. CHAR, VARCHAR).
    Finally, please can you post the exact SQL statement you are executing.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2010
    Posts
    22
    Quote Originally Posted by pootle flump View Post
    Please can you confirm what you mean by "blank"? Is the data actually NULL? (NULL has a meaning in an RDBMS, "blank" does not).
    Can you also confirm that these columns are INT and definitely not a character data type (e.g. CHAR, VARCHAR).
    Finally, please can you post the exact SQL statement you are executing.
    Thanks for the reply.

    By blank, I mean they are blank --- neither null nor zero. When I query, the result that comes back is simply a blank (not a space, not NULL, and not zero).

    The abbreviated code (C#) I'm using to INSERT is:

    Code:
    mysqlcommand.Parameters.AddWithValue("@PVer", CasePCPVerTextBox.Text);
    
    mysqlcommand.CommandText = "INSERT INTO CaseSpecs (PVersion) VALUES (@PVer)";
    
    mysqlcommand.ExecuteNonQuery();
    So that's how it's getting in.

    The query to compare is:

    Code:
    mysqlcommand2.Parameters.AddWithValue("@PVer", PVerTextBox.Text);
    
    mysqlcommand2.CommandText = "SELECT PVersion From CaseSpecs WHERE PVersion < @PVer";
    
    mysqlcommand2.ExecuteNonQuery();
    In this case, if I input say, 10, into PVerTextBox, it's giving me all records with less than 10. However, this also includes the blanks.

    These columns are NOT INT. They are nvarchar. Since we're dealing with versions of a program, there is the possibility of something like 1.1b. The idea is that in those cases, we're just gonna lop off the b and treat it as 1.1. That is done client-side though, that is taken care of and cleaned up before any query happens. None the less, the column cannot strictly be INT, as some records may have a letter. Everything outside of zero works fine, like if I specify greater than 10 but less than 20, I get exactly that, results where the version is greater than 10 but less than 20, even though they are nvarchar. No problems there. It's just the "blanks."

    Hopefully this clarifies.

    EDIT: my definition of "NULL" could be mistaken. Perhaps I'm interpreting the fact that it doesn't directly say NULL in my query results that it isn't NULL. For all I know these might be NULLs, I just don't see anything saying NULL when I query in Microsoft SQL 2008 R2 Server Management Studio. They just "appear" as blanks. If they are NULL, does that make a difference in this case? Is there some option somewhere to change that says like Count NULL as Zero?
    Last edited by CptSuperMrkt; 10-26-10 at 05:35.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - you have several problems I'm afraid.
    The term though is "zero length string" - this is correctly returned since "" is less than "10".
    Your comparison will fail in any event. Greater than and less than comparisons on strings of numbers will behave differently than comparisons of numeric data types.
    For example, 9 is less than 10. But "9" is greater than "10".

    Why could some values contain a letter? Can you give an example please?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2010
    Posts
    22
    Thanks, that helps to narrow it down. Now I have an actual term to use rather than just saying "blank."

    We're talking about versions of our internal software. Sometimes versions of software can include letters. For instance, if we're running 1.4, but then we might make a very small minor spelling change or something, we might not make the software 1.5, but instead like 1.4a or 1.4b. So that's why I can't just say the version field is always going to be strictly INTs, to allow for full flexibility when that occurs. That would make it too easy, wouldn't it?

    If it were INT, would that solve it? If it would be that simple of a fix, in that case, we could just deal in numbers and not record the "a" or "b" or whatever. We of course would be losing a slight bit of historical accuracy, by not having records of *exactly* what version our client is using.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Depends on the best solution for you really:
    You could divide this column into several columns e.g. major_version (TINYINT), minor_version (TINYINT), version_character (CHAR(1)).
    Or you could strip off characters at run time and convert to DECIMAL for the comparison purposes (this would of course run slowly though)
    Or you could do what you propose and change the column datatype to DECIMAL.

    Also, unless you have a need to store characters outside of the extended ASCII character set you shouldn't really use NVARCHAR either. Use VARCHAR.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You might want to see if you can create a canonical form for your version numbers based on the version numbers that you've used so far. If you can do that, then you ought to be able to create a function that will return the full canonical form (either zero or space filled) of any version string and that full form should sort properly.

    As an example, 192.168.1.1 is an IP address used by many end-user routers. The full canonical form for this address is 192.168.001.001 and that address will sort properly with other full canonical IP addresses. That way if you have a device with an address of 192.168.1.15 (which would sort after 192.168.1.1), the full canonical address of 192.168.001.015 would sort after 192.168.001.001 as you would probably expect.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •