Query to return middle text from a field

Question

I have an appointment that returns some class names/disciplines, but I wanted you to bring me only the names of disciplines. Below follows the query result with the default [class][discipline][subsidiary]:

ADM N1A OFFICIAL COMMUNICATION (MES)

ADM N1A PHILOSOPHY LOGIC AND ETHICS (MES)

ADM D1A OFFICIAL COMMUNICATION (AB)

ADM D1A PHILOSOPHY LOGIC AND ETHICS (AB)

ADM N1A PHILOSOPHY LOGIC AND ETHICS (AB)

PGER N1A SOCIAL AND LABOUR LEGISLATION (MES)

CCONT N1A ACCOUNTING THEORY (MES)

FIN N1A OFFICIAL COMMUNICATION (MES)

---

I noticed the following pattern: the name of the discipline starts just after the 2nd space and ends just before the last space (before the space preceding the "opens parentheses").

To the string: [FIN N1A OFFICIAL COMMUNICATION (MES)]

I wish to return only: [OFFICIAL COMMUNICATION]

I don’t know much about mysql, so I’ll get any help from my heart.

P.S. I NEED TO PASS THIS FILTER DIRECTLY IN THE QUERY (QUERY MYSQL)

Answer 1

You can use substring_index and replace deleting the first two snippets and the last one. The query below will return a column called resultado with the string treated:

select *,
trim(
   replace(
      replace(
         substring_index(
            substring(texto, instr(texto, " ")+1), " ", 10
         ),
         substring_index(
            substring(texto, instr(texto, " ")+1), " ", 1
         ), ''
      ),
      substring_index(
         substring(texto, instr(texto, " ")+1), " ", -1
      ), ''
   )
) as resultado
from tabela

See example on Sqlfiddle.