Query to return text from the middle of a field

Question

Query to return text from the middle of a field

Navigation

#1 by (2 votes)

1

I have a query that returns some class names / disciplines , however I wanted it to bring me just the names of the disciplines . Below is the result of the query with the [class] [discipline_name] [branch]:

ADM N1A OFFICIAL COMMUNICATION (MES)

ADM N1A LOGICAL AND ETHICAL PHILOSOPHY (MES)

ADM D1A OFFICIAL COMMUNICATION (AB)

ADM D1A LOGICAL AND ETHICAL PHILOSOPHY (AB)

ADM N1A LOGICAL AND ETHICAL PHILOSOPHY (AB)

PGER N1A SOCIAL AND LABOR LEGISLATION (MONTH)

CCONT N1A ACCOUNTING THEORY (MES)

END N1A OFFICIAL COMMUNICATION (MONTH)

I noticed the following pattern: the name of the course begins soon after the 2nd space and ends just before the last space (before the space that precedes the "open parentheses").

For the string: [END N1A OFFICIAL COMMUNICATION (MONTH))

I want to return only: [ OFFICIAL COMMUNICATION ]

I do not know much about mysql, so I'll get any help from my heart.

P.S. I NEED TO PASS THAT FILTER DIRECTLY IN QUERY MYSQL

mysql sql regex query mysql-workbench

asked by anonymous 09.02.2018 / 22:35

1 answer

Coverage of Unit Tests in Laravel Error trying to insert data to PostgreSQL [closed]

score 2 · Accepted Answer

You can use substring_index and replace by eliminating the first two and the last. The query below will return a column named resultado with the treated string:

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 in SQLFiddle .