March 20, 2006 at 4:51 am
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
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 20, 2006 at 4:58 am
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
The entries are not NULL. What makes you think they are? Are you sure they're not just empty ('')?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 20, 2006 at 5:11 am
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
Use this Query :
select * from tablea where col5=replace(col5,'null','')
Regards,
Amit Gupta
March 20, 2006 at 5:30 am
im afraid that made no difference
March 20, 2006 at 5:56 am
You can also use this :
select * from tableA where isnull(col5,'')=''
Regards ,
Amit Gupta
March 20, 2006 at 6:05 am
ive played around with that statement also and it returns nothing.thanks all the same
March 20, 2006 at 6:06 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 20, 2006 at 6:28 am
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
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.
Michael Lee
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply