Syntax error MySQL query to SELECT rows with LIKE and create new column containing the matched string?

MySQL query to SELECT rows with LIKE and create new column containing the matched string?

For this, use SUBSTRING(). Let us first create a table −

mysql> create table DemoTable1872
   (
   Name varchar(20)
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1872 values('John Doe');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1872 values('Adam Smith');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1872 values('Mitchell Johnson');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1872;

This will produce the following output −

+------------------+
| Name             |
+------------------+
| John Doe         |
| Adam Smith       |
| Mitchell Johnson |
+------------------+
3 rows in set (0.00 sec)

Following is the query to select rows with LIKE and create new column containing the matched string −

mysql> select Name,
   substring(Name, locate('John', Name), length('John')) as NewName
   from DemoTable1872
   where Name like '%John%';

This will produce the following output −

+------------------+---------+
| Name             | NewName |
+------------------+---------+
| John Doe         | John    |
| Mitchell Johnson | John    |
+------------------+---------+
2 rows in set (0.00 sec)
Updated on: 2019-12-27T06:09:28+05:30

289 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements