cant find null entries in a table, but i know they are there

  • i have a table and there is a column in it. most of the entries in it are numbers , but there are a few null entries, yet when i try

     

    select * from tableA

    where col5 is NULL

     

    i get no returned data even though there should be some. whats the problem?

    there are no errors being thrown up

  • Your syntax is fine - and very simple. Try inverting the query

    select * from tableA

    where col5 is not NULL

    and see whether the 'blanks' are returned.


  • yea, when i invert the query the null values are still returned. why is this? how do i sort this ?

  • The entries are not NULL. What makes you think they are? Are you sure they're not just empty ('')?


  • this sounds very dumb, but how do i search for empty cells?

     

    ive tride

    select * where col5 = ' '

    select * where col5 = ''

    select * where col5 = '%%'

     

    none work.

     

    im told

    Conversion failed when converting the varchar value 'NULL' to data type int.

    when i try to compare the col5 against numbers like 0 or where col5 < 0

  • Use this Query :

     

    select * from tablea where col5=replace(col5,'null','')

     

    Regards,

    Amit Gupta

     

  • im afraid that made no difference

  • You can also use this :

    select * from tableA where isnull(col5,'')=''

     

    Regards ,

    Amit Gupta

  • ive played around with that statement also and it returns nothing.thanks all the same

  • Hang on. VARCHAR value 'NULL'?? Suggests that this might work:

    select * from tableA

    where col5 = 'NULL'

    If so, it's not empty - it's a string set to NULL.


  • thank you phil, that worked a treat. dont know how i was so stupid not to see that, but i suppose these things can be overlooked ( especially in a panic!!)

    thanks again..

  • I know Phil provided an answer, but just to beat a dead horse, there is one more query,

    SELECT & FROM TableA WHERE RTRIM(col5)=''

    will pick up if col5 has only spaces.

     


    Shalom!,

    Michael Lee

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply