How to select rows with condition via concatenate in MySQL?

For this, you can use CONCAT_WS(). Let us create a table −

mysql> create table demo38
−> (
−> user_id int,
−> user_first_name varchar(20),
−> user_last_name varchar(20),
−> user_date_of_birth date
−> );
Query OK, 0 rows affected (1.70 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo38 values(10,'John','Smith','1990−10−01');
Query OK, 1 row affected (0.14 sec)

mysql> insert into demo38 values(11,'David','Miller','1994−01−21');
Query OK, 1 row affected (0.13 sec)

mysql> insert into demo38 values(11,'John','Doe','1992−02−01');
Query OK, 1 row affected (0.13 sec)

mysql> insert into demo38 values(12,'Adam','Smith','1996−11−11');
Query OK, 1 row affected (0.11 sec)

mysql> insert into demo38 values(13,'Chris','Brown','1997−03−10');
Query OK, 1 row affected (0.13 sec)

Display records from the table using select statement −

mysql> select *from demo38;

This will produce the following output −

+---------+-----------------+----------------+--------------------+
| user_id | user_first_name | user_last_name | user_date_of_birth |
+---------+-----------------+----------------+--------------------+
| 10      | John            | Smith          | 1990−10−01         |
| 11      | David           | Miller         | 1994−01−21         |
| 11      | John            | Doe            | 1992−02−01         |
| 12      | Adam            | Smith          | 1996−11−11         |
| 13      | Chris           | Brown          | 1997−03−10         |
+---------+-----------------+----------------+--------------------+
5 rows in set (0.00 sec)

Following is the query to select rows with condition −

mysql> select concat_ws('/',user_first_name, user_last_name,'the date of birth year is=', date_format(user_date_of_birth,'%Y')) as Output
−> from demo38
−> where user_id in(11,13);

This will produce the following output −

+----------------------------------------------+
| Output                                       |
+----------------------------------------------+
| David/Miller/the date of birth year is=/1994 |
| John/Doe/the date of birth year is=/1992     |
| Chris/Brown/the date of birth year is=/1997  |
+----------------------------------------------+
3 rows in set (0.00 sec)

AmitDiwan

e

Updated on: 19-Nov-2020

150 Views

Kickstart Your Career

Get certified by completing the course

Get Started