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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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 ('')?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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 11 (of 11 total)

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