Post reply

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

  • March 20, 2006 at 4:51 am

    #112395

    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

  • March 20, 2006 at 4:55 am

    #627552

    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.

  • March 20, 2006 at 4:58 am

    #627553

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

  • March 20, 2006 at 5:07 am

    #627555

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

  • March 20, 2006 at 5:11 am

    #627556

    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

  • March 20, 2006 at 5:20 am

    #627557

    Use this Query :

     

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

     

    Regards,

    Amit Gupta

     

  • March 20, 2006 at 5:30 am

    #627561

    im afraid that made no difference

  • March 20, 2006 at 5:56 am

    #627564

    You can also use this :

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

     

    Regards ,

    Amit Gupta

  • March 20, 2006 at 6:05 am

    #627567

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

  • March 20, 2006 at 6:06 am

    #627568

    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.

  • March 20, 2006 at 6:28 am

    #627573

    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..

  • March 21, 2006 at 7:30 am

    #627863

    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