NOT NULL and Spaces

Question

Post reply

NOT NULL and Spaces

dale_keller

Say Hey Kid

Points: 713
More actions
November 4, 2008 at 2:37 pm

#96763

Hi,
I'm trying to eliminate all rows where the column is either NOT NULL or contains spaces. So my query is something like this:
SELECT * FROM MYTABLE
WHERE (STATUS IS NOT NULL OR STATUS <> ' ')
The query eliminates the NULL's but still returns back the column with spaces.
Any ideas on what I'm doing wrong would be greatly appreciated??

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

November 4, 2008 at 2:53 pm

#893917

Change the OR to an AND.

nageswara.dhaveji SSChasing Mays Points: 601 More actions · Answer 2

Try this..

select * from mytable where isnull(ltrim(rtrim(status)),'')<>''

dale_keller Say Hey Kid Points: 713 More actions · Answer 3

dale_keller

Say Hey Kid

Points: 713

November 5, 2008 at 7:27 am

#894228

Thanks ever so much. That worked great.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

WHERE STATUS>''

Madhivanan

Failing to plan is Planning to fail

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

dale_keller (11/5/2008)
Thanks ever so much. That worked great.

Which way did you fix your problem?

dale_keller Say Hey Kid Points: 713 More actions · Answer 6

I used this as an example:

select * from mytable where isnull(ltrim(rtrim(status)),'')<>''

works great!!!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Sounds good. Again, just curious, did you try changing the OR to AND in your original query?

dale_keller Say Hey Kid Points: 713 More actions · Answer 8

dale_keller

Say Hey Kid

Points: 713

November 5, 2008 at 10:30 am

#894415

Yes, and it still did not work.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 9

Lynn yours would work if you LTRIM RTRIM the status first.

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Christopher Stobbs (11/5/2008)
Lynn yours would work if you LTRIM RTRIM the status first.

I thought so. I just attacked the obvious BOOLEAN error in the original code and didn't really notice that there could have been values with multiple spaces (ie ' ') since the code had status <> '' in the where clause.