Syntax error Display and concatenate records ignoring NULL values in MySQL

Display and concatenate records ignoring NULL values in MySQL

Use CONCAT() to concatenate records whereas IFNULL() to check for NULL values.

Let us first create a table −

mysql> create table DemoTable802 (
   FirstName varchar(100),
   LastName varchar(100)
);
Query OK, 0 rows affected (1.01 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable802 values('Adam','Smith');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable802 values('Carol',NULL);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable802 values(NULL,'Taylor');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable802 values(NULL,NULL);
Query OK, 1 row affected (0.21 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable802;

This will produce the following output -

+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| Adam      | Smith    |
| Carol     | NULL     |
| NULL      | Taylor   |
| NULL      | NULL     |
+-----------+----------+
4 rows in set (0.00 sec)

Following is the query to concatenate records ignoring the NULL values −

mysql> select concat(IFNULL(FirstName,''),' ',IFNULL(LastName,'')) AS FULL_NAME from DemoTable802;

This will produce the following output -

+------------+
| FULL_NAME  |
+------------+
| Adam Smith |
| Carol      |
| Taylor     |
|            |
+------------+
4 rows in set (0.00 sec)
Updated on: 2019-09-09T09:08:45+05:30

800 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements