How to say not an empty string in MYSQL with Regular Expression

Question

I am wondering how could i say not an empty string in MYSQL with Regular Expression.

My thought :

SELECT * FROM `table` WHERE `column` <> '^$' 

I am totally newby as far as Regular Expressions are concerned. And not a pro at all in MySQL.

Answer 1

Use LENGTH():

SELECT * FROM table
WHERE LENGTH(column) > 0

This (correctly) returns rows that are one or more spaces, and doesn't return nulls.

---

Note that

WHERE column <> ''

behaves differently. <> ignores trailing spaces, so a column that contains (only) spaces will be considered the same as '', so such rows will be excluded from the selection. If that is what you want, then you can either do:

SELECT * FROM table
WHERE column <> ''

OR

SELECT * FROM table
WHERE LENGTH(TRIM(column)) > 0

Either way, a column containing NULL will evaluate the WHERE expression to NULL, which will exclude the column from the selection. (It is not necessary to also do "AND column IS NOT NULL")

Answer 2

The solution depends on whether you want columns containing only blanks to be considered "empty".

To consider blanks to be empty, and exclude them from the selection, do:

SELECT * FROM `table` WHERE `column` <>  '' AND `column` IS NOT NULL

NOTE: TRIM(column) is not needed here, because <> ignores trailing blanks. However, if you feel that TRIM(column) makes the intent clearer, there is no harm in including it:

 SELECT * FROM `table` WHERE TRIM(`column`) <>  '' AND `column` IS NOT NULL

This has exactly the same result as the first version.

---

To consider blanks to not be empty, and include them in the selection, use Bohemian's accepted answer.

---

If you really want use REGEX you should check this

SELECT * FROM `table` WHERE `column` REGEX '^$' AND `column` IS NOT NULL

But I don't recommend using REGEX for checking if string is empty.

---

UPDATE:
In all of the above answers, "AND column IS NOT NULL" could be omitted. A column containing NULL will evaluate the WHERE expression to NULL, which will exclude the column from the selection.

So the same result can be obtained with the simpler:

SELECT * FROM `table` WHERE `column` <>  ''

Answer 3

This is not a comparison to regular expression:

SELECT * FROM `table` WHERE `column` <> '^$' 

This is:

SELECT * FROM `table` WHERE `column` REGEX '^$' 

or

SELECT * FROM `table` WHERE `column` RLIKE '^$' 

One of the first things in learning about regular expressions is when to use them and when not to. This is a case not to. You should just do:

SELECT * FROM `table` WHERE `column` <> '' 

By the way, all of these comparisons automatically fail when the value is NULL. If you want to allow NULL values, you would have to do that explicitly.