Results 1 to 13 of 13

Thread: Nulls

  1. #1
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19

    Unanswered: Nulls

    Can someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?

    Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.

  2. #2
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58

    Cool Re: Nulls

    to locate all records having nulls in a talbe:
    select * from table where column_name is null

    to delete all rows that contain nulls:
    delete from table where column_name is null


    Richard

    Originally posted by Arra2
    Can someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?

    Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.

  3. #3
    Join Date
    Jan 2004
    Posts
    164

    Re: Nulls

    Originally posted by Arra2
    Can someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?

    Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.
    Do you want to delete the whole record that contains the nulls?

    you can find all teh nulls by usuing:

    select * from tableName
    where whateverField = '0'

    Just fill in the tableName part with the table that you are looking in and
    fill in the whateverField part with the name of the column that the nulls appear in.

  4. #4
    Join Date
    Jan 2004
    Posts
    164

    Re: Nulls

    Originally posted by estefex
    Do you want to delete the whole record that contains the nulls?

    you can find all teh nulls by usuing:

    select * from tableName
    where whateverField = '0'

    Just fill in the tableName part with the table that you are looking in and
    fill in the whateverField part with the name of the column that the nulls appear in.
    Correction on how to find nulls:

    select * from tableName
    where whateverField is null

  5. #5
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19

    NULLS

    Sory i didn't explain in more detail. i can find the NULLS no problem.
    Once I find the NULLS I would like to delete the NULLS ONLY and not the whole column where the NULLS are located. I want to replace the viewable NULL with nothing.

    I would like to delete the NULL but keep everything else intact.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The only way to make the NULL values go away without deleting the row is to replace the NULL with another value, something like
    PHP Code:
    UPDATE myTable
       SET dateCol 
    GetDate()
       
    WHERE  dateCol IS NULL 
    The only way to make the NULL value "go away" is to put something else in its place.

    -PatP

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

    Re: NULLS

    Originally posted by Arra2
    I want to replace the viewable NULL with nothing.
    i think this is the real problem

    Arra2 is using some software to display table contents, and that software is substituting something for the nulls

    e.g. the word NULL

    how about using COALESCE in your queries?
    Code:
      select coalesce(nullablecharfield,'') as displayablecharfield
    of course, if you have a nullable numeric field, you have another problem on your hands, don't you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19

    NULL

    I've tested your information and it's what I'm looking for. Thanks.

    But what if I what the NULL to be replaced with nothing. I want a blank field.

    If I try

    Update My_table_name
    Set My_Column_name =
    Where My_Column_Name is Null

    I recieve an error. I want to replace the Null with a blank field.

    Thanks for the help up to this point.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yup, numeric, datetime, and bit will be a problem. But I think he's talking about character NULLs. How about char(39)+char(39)?

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

    Re: NULL

    Originally posted by Arra2
    But what if I what the NULL to be replaced with nothing.
    um, i think you might want to re-think this

    NULL is nothing

    a "blank" field contains a string

    it's a zero-length string to be sure, but it's not nothing
    I want to replace the Null with a blank field.
    ah, well, if you insist --
    PHP Code:
    update my_table_name
       set my_column_name 
    ''
     
    where my_column_name is null 
    you're still gonna have trouble with numeric fields, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If you are going to go through all this trouble:

    1. Redesign so you don't have NULL attributes in your entities.
    or
    2. Just learn how to use NULL. Especially if you are wanting to use this for all kinds of datatypes like integer, money, etc.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  12. #12
    Join Date
    Oct 2003
    Posts
    706

    Exclamation Re: Nulls


    Correction on how to find nulls:
    select * from tableName
    where whateverField is null
    It is extremely important to notice the fundamental difference between the original test (whateverField = '0') and this, correct one.

    NULL is defined as "the absence of any value at all." The value in the field is not zero, nor any other value: it is, "no value at all." Therefore, it is not "equal to" (nor, for that matter, "unequal to") any value at all.

    Consider a table with a person's name and his age. Tom is 50 and Dick is 30, but Mary declined to tell us her age. Therefore, the age-column in her record is for the moment NULL. If we then "SELECT ... AVERGAE(AGE)" we'll get the correct value: 40. SQL took the two values which did exist (50 and 30), added them up, divided by the count of values which did exist (2, not 3), and produced the expected result. This is fundamentally important in statistics, where NULL conveniently represents a "missing value."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  13. #13
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19

    NULLS

    Thanks for all those who have helped. I think I have it under control.

    Thanks,

    SQL Rookie

Posting Permissions

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