MySQL regex - Get text in one query

Question

Assume we have a table like this in MySQL

tbl_test

-----------------------------------------------------
| ID | text                                         |
-----------------------------------------------------
| 1  | Lorem ipsum \n Teacher: Mr. Brown \n Age: 43 | 
| 2  | Dolor \n Teacher: Mrs. Morgan \n Age: 35     | 
-----------------------------------------------------

Is it possible to get the name of the teachers with one single SQL Query. The expected result should be:

 Mr. Brown
 Mrs. Morgan

I thought of something like a regex SQL query. I have already tried it with LIKE but then I get the whole text and not only the teacher's name.

SELECT text FROM tbl_test WHERE text LIKE '%Teacher%';

Output

Lorem ipsum \n Teacher: Mr. Brown \n Age: 43
Dolor \n Teacher: Mrs. Morgan \n Age: 35

Your're probably better off fetching the whole text and use php's `explode` or javascript's `split` to get the part you want — JordyvD, May 15 '15 at 07:55
possible duplicate of [MySQL - Return matching pattern in REGEXP query](http://stackoverflow.com/questions/5361457/mysql-return-matching-pattern-in-regexp-query) — npinti, May 15 '15 at 07:56
possible duplicate of [How to regex in a sql query](http://stackoverflow.com/questions/18780194/how-to-regex-in-a-sql-query) — BeNdErR, May 15 '15 at 07:57
The better solution is to extract the name _before_ storing into the table. Then have a column for `name`. — Rick James, May 15 '15 at 16:06

score 1 · Accepted Answer · answered May 15 '15 at 07:56

If the pattern is same you can use substring_index

mysql> select substring_index(substring_index('Lorem ipsum \n Teacher: Mr. Brown \n Age: 43','Teacher:',-1),'\n',1) as teacher;
+-------------+
| teacher     |
+-------------+
|  Mr. Brown  |
+-------------+
1 row in set (0.00 sec)



select
substring_index(substring_index(text,'Teacher:',-1),'\n',1) as teacher_name
from tbl_test ;

MySQL regex - Get text in one query

1 Answers1