Need help selecting non-empty column values from MySQL?

MySQLMySQLi Database

Select non-empty column values using NOT IS NULL and TRIM() function. The syntax is as follows.

SELECT * FROM yourTableName WHERE yourColumnName IS NOT NULL AND TRIM(yourColumnName) <> ' ';

You can select non-empty value as well as whitespace from column using the same TRIM() function.

To understand the syntax we discussed above, let us create a table. The query to create a table is as follows −

mysql> create table SelectNonEmptyValues
   -> (
   -> Id int not null auto_increment,
   -> Name varchar(30),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.62 sec)

Insert records in the table using insert command. The query is as follows −

mysql> insert into SelectNonEmptyValues(Name) values('John Smith');
Query OK, 1 row affected (0.20 sec)

mysql> insert into SelectNonEmptyValues(Name) values(NULL);
Query OK, 1 row affected (0.13 sec)

mysql> insert into SelectNonEmptyValues(Name) values('');
Query OK, 1 row affected (0.24 sec)

mysql> insert into SelectNonEmptyValues(Name) values('Carol Taylor');
Query OK, 1 row affected (0.13 sec)

mysql> insert into SelectNonEmptyValues(Name) values('DavidMiller');
Query OK, 1 row affected (0.28 sec)

mysql> insert into SelectNonEmptyValues(Name) values(' ');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement −

mysql> select *from SelectNonEmptyValues;

The following is the output −

+----+-----------------------+
| Id | Name                  |
+----+-----------------------+
|  1 | John Smith            |
|  2 | NULL                  |
|  3 |                       |
|  4 | Carol Taylor          |
|  5 | DavidMiller           |
|  6 |                       |
+----+-----------------------+
6 rows in set (0.00 sec)

Here is the query to select non-empty values. The below query works for all cases − if the column has NULL, empty string or whitespaces −

mysql> SELECT * FROM SelectNonEmptyValues WHERE Name IS NOT NULL AND TRIM(Name) <> '';

The following is the output −

+----+--------------+
| Id | Name         |
+----+--------------+
|  1 | John Smith   |
|  4 | Carol Taylor |
|  5 | DavidMiller  |
+----+--------------+
3 rows in set (0.00 sec)
raja
Published on 31-Jan-2019 12:25:44
Advertisements