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.
4 Answers
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")
2 Comments
Stefanos Vakirtzis
This actually works , thank you very much for your help. I will accept the answer in 5 minutes. :)
Stefanos Vakirtzis
Perhaps it was me downvoting by mistake. I will change this when time wise i am eligible to do so. Thanks again.
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` <> ''
7 Comments
Bohemian
-1 your first answer will not return rows where
column is a series of spaces - see comment to question.
Robert
@Bohemian sure but it wasn't mentioned in question. It was in comments after I posted my answer.
Bohemian
but a series of blanks is not the empty string, no matter the comments. it's in the first line of the question in bold!
Robert
My answer doesn't check series of blanks it checks if string is empty. If string contains spaces then it's not empty and this is what the bold part of question is about. At least in my opinion.
Bohemian
You have just edited the query to add
trim(), which fixes the error. |
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.
Comments
fieldname REGEXP '^$|^[[:blank:]]+$|^[[:space:]]+$' OR fieldname IS NULL
1 Comment
Nico Haase
Please add some explanation to your answer such that others can learn from it
WHERE 'column' <> '';^.+$will match one or more characters. That being said, I would recommend to sticking to some of the answers below.WHERE 'column' <> '';wont do cause this statement will be a part of series of imploded and exploded arrays being cut and added in various combinations.