SQL not fetching empty columns

Question

i have a table that looks like this

students
----------
ID  | Name  | Class  | Pass
01  | Lisa  | 01     | 1D345
02  | Mike  | 03     | 22341
03  | Kim   | 03     | 
04  | Lance | 04     | 193CC

So I wanted to select those where Pass is not empty, and I've tried

SELECT * FROM students WHERE Pass IS NOT NULL;

which returned the same table where it should have fetched me this

students
----------
ID  | Name  | Class  | Pass
01  | Lisa  | 01     | 1D345
02  | Mike  | 03     | 22341
04  | Lance | 04     | 193CC

I've also tried

SELECT * FROM students WHERE Pass IS NOT '';

and

SELECT * FROM students WHERE Pass !='';

both returning #1064 error, so what is the statement I should be using the fetch the required table?

As a rule both returning error isn't much help, unless you actually include the error message. — MatBailie
– MatBailie, Commented Oct 11, 2018 at 11:46

Gordon Linoff · Accepted Answer · 2018-10-11 11:33:56Z

1

Presumably, pass has a value other than NULL. This might work:

WHERE Pass <> ''

This is equivalent to your last option, which should work in most databases (most databases support both <> and != for inequality).

Or you might need to deal with spaces.

In MySQL, you can use a regular expression to be sure there is at least one alphanumeric character:

where pass regexp '[a-zA-Z0-9]'

answered Oct 11, 2018 at 11:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL not fetching empty columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related