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

Question

I need some help with a MySQL query I am struggling for some time now.

So, I am trying to create a MySQL query to SELECT rows from a table that match a specific string like app. My table is like this:

+-----+--------------+
| id  | name         |
+-----+--------------+
|   1 | Green Apple  |
|   2 | Big Orange   |
|   3 | application  |
+-----+--------------+

I can find all rows that contain app string with SELECT and LIKE.

However, I also want to create new column that contains the string from name column which matches app and keep the database case sensitive format, i.e. with app as a match phrase the new column will contain App and app entires according to the string format in name.

My query so far goes like this:

SELECT *, 'what_to_put?' as new_column FROM table WHERE name LIKE '%".$app."%'

The desired output is the following:

+-----+--------------+-------------+
| id  | name         | new_column  |
+-----+--------------+-------------+
|   1 | Green Apple  |     App     |
|   2 | application  |     app     |
+-----+--------------+-------------+

Any idea how to achieve this?

Answer 1

Without a separate regex library, you'll need to use the built-in string functions to find the location of the match, and then extract the matching sub-string:

SELECT 
  id, 
  name, 
  substring(name, locate('app', name), length('app')) as new_column 
FROM yourTable 
WHERE name LIKE '%app%'

Which gives the results:

+----+-------------+------------+
| id |    name     | new_column |
+----+-------------+------------+
|  1 | Green Apple | App        |
|  3 | application | app        |
+----+-------------+------------+

Sql Fiddle Here