MySQL regex - Get text in one query -

assume have table 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 possible name of teachers one single sql query. expected result should be:

 mr. brown  mrs. morgan 

---

i thought of regex sql query. have tried like whole text , not teacher's name.

select text tbl_test text '%teacher%'; 

output

lorem ipsum \n teacher: mr. brown \n age: 43 dolor \n teacher: mrs. morgan \n age: 35 

if pattern same can use substring_index

mysql> select substring_index(substring_index('lorem ipsum \n teacher: mr. brown \n age: 43','teacher:',-1),'\n',1) teacher; +-------------+ | teacher     | +-------------+ |  mr. brown  | +-------------+ 1 row in set (0.00 sec)    select substring_index(substring_index(text,'teacher:',-1),'\n',1) teacher_name tbl_test ;