
How to find rows with empty column value in MySQL/MariaDB table (in phpMyAdmin and in command line)
August 16, 2024
How to find rows with empty column value in phpMyAdmin
Suppose you want to find rows in a table where the value of a certain column is empty. To do this, you try to use the table search in phpMyAdmin.
If you go to the “Search” tab in phpMyAdmin and click the “Go” button without entering any values (since you need to find an empty row), it will not work – the entire table will be displayed.
However, phpMyAdmin has the ability to find rows with empty field values. To do this, as the “Operator” of the field whose empty values you are looking for, select the following:
= ''
This will display rows with an empty field value.
There is another way to find empty field values. To do this, select “REGEXP” as the “Operator” of the field whose empty values you are searching for, and enter the following as the “Value”:
^$
REGEXP means to use regular expressions for searching. And the “^$” symbols mean “empty string”.
The result will show rows with an empty field value.
SQL query to find rows with an empty column value in MySQL/MariaDB
The following query searches the TestTABLE table for rows in which the test_column column value is empty (note that here and below at the end of the query there is not one double quote, but two single quotes):
SELECT * FROM `TestTABLE` WHERE `test_column` = '';
Instead of the equal sign, you can use the LIKE operator – in this case, the meaning of the SQL queries is identical:
SELECT * FROM `TestTABLE` WHERE `test_column` LIKE '';
An empty string in regular expressions is denoted as ^$, that is, if you want, you can use the following query with REGEXP to search for empty strings:
SELECT * FROM `TestTABLE` WHERE `test_column` REGEXP '^$';
Related articles:
- How to display results in MySQL and MariaDB without a table and without extraneous characters (77.1%)
- How to list columns in MySQL/MariaDB. How to check if a column exists in a table in PHP (65.6%)
- “Failed - Network error” when exporting from phpMyAdmin (SOLVED) (58.9%)
- How to rename a table in phpMyAdmin and MySQL (58.9%)
- ERROR at line 1: Unknown command '\-'. (SOLVED) (58.9%)
- How to connect a TV to a computer in Linux in GNOME (Ubuntu) (RANDOM - 50%)