Syntax error Concatenate two columns when one of such column values is null in MySQL

Concatenate two columns when one of such column values is null in MySQL

To avoid any issues while running a query, use IFNULL(). Let us first create a table −

mysql> create table DemoTable1793
     (
     StudentFirstName varchar(20),
     StudentLastName varchar(20)
     );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1793 values('John','Smith');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1793 values('Carol',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1793 values(NULL,'Brown');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1793;

This will produce the following output −

+------------------+-----------------+
| StudentFirstName | StudentLastName |
+------------------+-----------------+
| John             |           Smith |
| Carol            |            NULL |
| NULL             |           Brown |
+------------------+-----------------+
3 rows in set (0.00 sec)

Here is the query to concatenate two columns when one of such column values is null −

mysql> select concat(ifnull(StudentFirstName,''),ifnull(StudentLastName,'')) from DemoTable1793;

This will produce the following output −

+----------------------------------------------------------------+
| concat(ifnull(StudentFirstName,''),ifnull(StudentLastName,'')) |
+----------------------------------------------------------------+
| JohnSmith                                                      |
| Carol                                                          |
| Brown                                                          |
+----------------------------------------------------------------+
3 rows in set (0.00 sec)
Updated on: 2019-12-23T12:00:01+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements