I want to collect all the rows with column A not null and not blank.

Question

I used the following:

AND A.EXP_GRAD_TERM is not null AND A.EXP_GRAD_TERM <> ' '

It gave me 1169 records with some values for the field.

The moment I used this:

AND LTRIM(RTRIM(A.EXP_GRAD_TERM)) is not null

It gave me 1932 records. They have rows with values and blanks. I converted the column to only blanks or the values, not unwanted characters.

Any opinions on this?

Answer 1

The expression gave down below:

LTRIM(RTRIM(A.EXP_GRAD_TERM)) is not null

Is similar or equivalent to:

A.EXP_GRAD_TERM is not null

An empty string is completely different from NULL, therefore removing spaces from that string which only has spaces makes no effect on the NULL comparison.

I think you are confused because you must have experienced Oracle where empty strings and NULL are both the same thing.

If you want to get more insights into SQL, check out this SQL Course from Intellipaat.