CODEWITHSUNDEEP
mysql
Nate
Nate

Reputation: 28334

How to select non-empty columns in MySQL?

Sorry if this has been asked before, but I've been searching and can't find a solution to my simple problem.

I need to select all rows where a varchar column in it has text in it (letters, numbers, or special characters), so I wrote a query like the following:

SELECT col1
FROM table
WHERE col1 IS NOT NULL

But this returns columns that contain the value '' (i.e. it's blank, but is not set to NULL so it still gets returned).

I tried changing the query to this:

SELECT col1
FROM table
WHERE col1 IS NOT NULL
AND col1 != ''

But that didn't work.

How do I stop the rows where the column is blank from being returned?

Upvotes: 13

Views: 33047

Answers (3)

sj59
sj59

Reputation: 2162

use LENGTH :

SELECT col1
FROM table
WHERE LENGTH(col1) > 0

Upvotes: 1

Shankar Kalyankar
Shankar Kalyankar

Reputation: 249

You can use this query if column is blank, but is not set to NULL

SELECT col1 FROM table where col1 not like ''; # Two Single quotes without space

OR

This if column has one or more spaces,

SELECT col1 FROM table where col1 not like '% %';

Upvotes: 1

Rab
Rab

Reputation: 35572

use TRIM there seems to be spaces in your column then

SELECT col1
FROM table
WHERE col1 IS NOT NULL
AND TRIM(col1) <> ''

Upvotes: 26

Related Questions