Loading...
X

How to find rows with empty column value in MySQL/MariaDB table (in phpMyAdmin and in command line)

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 '';

See also: What is the difference between the LIKE operator and the equal sign (=) in MySQL/MariaDB. How to use LIKE in SQL

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 '^$';


Leave Your Observation

Your email address will not be published. Required fields are marked *