How to select non-empty columns in MySQL?

Question

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?

Answer 1

use LENGTH :

SELECT col1
FROM table
WHERE LENGTH(col1) > 0

Answer 2

use TRIM there seems to be spaces in your column then

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