Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74

    Unanswered: Problem testing for "<NULL>" in an SQL Server database table column.

    I am puzzled. I have a column “CalloutName” in one of the tables in an SQL Server database. That column “CalloutName” has either a string or a NULL (as in “<NULL>”).

    (A) If there is no gap/space between the entry <NULL> and the left boundary of a column, the entry is treated as a NULL.
    (B) If there is a gap/space between the entry <NULL> and the left boundary of a column, the entry is treated as not NULL.

    (A) and I can use the following to test whether it is really a NULL:

    If IsNull(!CalloutName) Or !CalloutName = "" Then
    callOutState = "" (path for A)
    Else
    callOutState = !CalloutName (path for B)
    End If

    So, I tried the following code to see whether the entry in (B) can be picked up as a NULL.

    Dim iPosNULL As Integer
    iPosNULL = InStr(4, !CalloutName, "NULL>", 1)

    If IsNull(!CalloutName) Or !CalloutName = "" Or iPosNULL <> 0 Then
    callOutState = ""
    Else
    callOutState = !CalloutName
    End If

    I got an error:

    and got an error: “Inavalid use of NULL”.

    So, I am at a loss; how do I test the Nullity of such an entry.

  2. #2
    Join Date
    Jul 2002
    Posts
    55
    Are you physically writing the string "<NULL>" to the database?

    When you look at a table in data view, if you see a column with the string "<NULL>" in it, it isn't a physical string. SQL Server is indicating a NULL value in that field.

    Therefore:

    Code:
    iPosNULL = InStr(4, !CalloutName, "NULL>", 1)
    Will throw an exception of "Invalid use of NULL" as you can't pass a NULL value to InStr().

    It looks like you're checking for a string where there isn't one. Try just using IsNull().

    Mull.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    how to test for nulls

    something's not quite right

    you said the column has either a string or a NULL (as in “<NULL>”)

    but "<NULL>" is a string

    and "" is a string, too (a zero-length string), and it's not the same as null either

    a real null cannot be shown

    whenever you run a query, the interface, whether it's Query Manager, ASP, or whatever, has to translate the null into something that it can show you

    if you want to know for sure whether the column has any real nulls in it, run this query --
    Code:
    select COALESCE(CalloutName,'yup, a real null')
      from yourtable
    rudy
    http://rudy.ca/

  4. #4
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    Originally posted by Mulligan
    Are you physically writing the string "<NULL>" to the database?

    When you look at a table in data view, if you see a column with the string "<NULL>" in it, it isn't a physical string. SQL Server is indicating a NULL value in that field.

    Therefore:

    Code:
    iPosNULL = InStr(4, !CalloutName, "NULL>", 1)
    Will throw an exception of "Invalid use of NULL" as you can't pass a NULL value to InStr().

    It looks like you're checking for a string where there isn't one. Try just using IsNull().

    Mull.
    Thansk for your response. The issue is not wheter I can tell wheter what I read is a NULL or not. If <NULL> in is a column and the "<" part of it occupies the first position in the column as in |<NULL> |, I can tell it is a null.
    If <NULL> in is a column and the "<" part of it DOES not occupy the first position in the column as in | <NULL> |, I cannot tell it is a null; it comes a "<NULL>" string. Since I am not the one to populate the database, I have no choice but to figureout how to read the NULL whichever way it comes accross.

    In the paragraph above, "|" represent the column bars.

    So, the issue is:

    if <NULL> is aligned in the first position, I can read it as a NULL using

    If IsNull(!CalloutName) Or !CalloutName = "" Then
    callOutState = "" (path for A)
    Else
    callOutState = !CalloutName (path for B)
    End If

    if <NULL> is aligned in a position other than the first position, I cannot read it as a NULL using the code fragment above.

  5. #5
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74

    Re: how to test for nulls

    Originally posted by r937
    something's not quite right

    you said the column has either a string or a NULL (as in “<NULL>”)

    but "<NULL>" is a string

    and "" is a string, too (a zero-length string), and it's not the same as null either

    a real null cannot be shown

    whenever you run a query, the interface, whether it's Query Manager, ASP, or whatever, has to translate the null into something that it can show you

    if you want to know for sure whether the column has any real nulls in it, run this query --
    Code:
    select COALESCE(CalloutName,'yup, a real null')
      from yourtable
    rudy
    http://rudy.ca/
    Thanks for your response. I will try what you suggested. And please read the response to Mulligan above; may be it will shed more light on what my probelm is.

  6. #6
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74

    Re: how to test for nulls

    Originally posted by r937
    something's not quite right

    you said the column has either a string or a NULL (as in “<NULL>”)

    but "<NULL>" is a string

    and "" is a string, too (a zero-length string), and it's not the same as null either

    a real null cannot be shown

    whenever you run a query, the interface, whether it's Query Manager, ASP, or whatever, has to translate the null into something that it can show you

    if you want to know for sure whether the column has any real nulls in it, run this query --
    Code:
    select COALESCE(CalloutName,'yup, a real null')
      from yourtable
    rudy
    http://rudy.ca/
    Your fragment:

    select COALESCE(CalloutName,'yup, a real null')
    from yourtable

    helped me confirm that althoush there is <NULL> in tha column, it does not mean that it will be intepreted as a NULL. What I meas is that in some cases, that fragment produced "<NULL>", ohter strings and "yup, a real null".

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so this means you have real nulls, as well as strings pretending to be nulls

    any zero-length strings?

    you never said what you wanted to do with those various things

    assuming you want to handle them separately from "real" values, use this query --
    Code:
    select CASE 
            WHEN CalloutName IS NULL 
              THEN 'oops'
            WHEN CalloutName LIKE '%<NULL>%'
              THEN 'oops'
            WHEN CalloutName = ''
              THEN 'oops'
            ELSE CalloutName
           END as CalloutName 
      from yourtable
    you can handle them all in one, or separately if you assign different "oops" strings

    again, easier to do it in the sql, less mess in your calling code

    by the way, the COALESCE i gave you is functionally identical to
    Code:
    SELECT CASE 
            WHEN CalloutName IS NULL 
              THEN 'yup, a real null'
            ELSE CalloutName
           END as CalloutName
    rudy

  8. #8
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    Thanks a ga-zillion Rudy. You made my day. The next quetion is: where can I learn such nifty tricks, i.e, such SQL language" Is there a food book or website?

    Wango

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good question

    i have a bunch of web resources bookmarked (via Powermarks by http://kaylon.com/ -- highly recommended), and i'm slowly copying them to a database and making them available on my site

    see http://r937.com/links.cfm?links=sql

    i'm right in the middle of moving my sites from one host to another, so it's possible that many pages will not be working at the moment -- but that one does

    rudy

Posting Permissions

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