CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Monomoni
Monomoni

Reputation: 435

SQL not fetching empty columns

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?

Upvotes: 0

Views: 112

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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]'

Upvotes: 1

Related Questions