How do I get the values ​between these fields with format?

Question

I am doing a db migration I need to pass data from db1 to db2. I am getting a sql query result similar to this. Formats like and [:es] or represent the language.

name
------------
<!--:es-->Nombre<!--:--><!--:en-->Name<!--:-->
<!--:es-->Ciudad<!--:--><!--:en-->City<!--:-->
<!--:es-->Pais<!--:--><!--:en-->Country<!--:-->
<!--:es-->Email<!--:--><!--:en-->Email<!--:-->
<!--:es-->Telefono<!--:--><!--:en-->Phone<!--:-->
[:es]Apodo[:en]Nikname[:]
[:es]Tipo[:en]Type[:]
[:es]Categoria[:en]Category[:]
[:es]Distribuidor[:en]Distributor[:]
Value 1
Value 2
Value 3

Some data comes with format <!--:es-->Field<!--:es-->...

Others comes with format [:es]Apodo[:en]Nikname[:]

And some with no special format. For example Value 1, Value 2 and Value 3.

I need only the name, without any special delimiter. Any idea how to achieve this.

The expected result is

name
------------
Nombre
Ciudad
Pais
Email
Telefono
Apodo
Tipo
Categoria
Distribuidor
Value 1
Value 2
Value 3

Update

My sql version is

mysql -V 
mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper

Answer 1

I got to handle this case in the following way

With the help of case when, first I identify the type of format, using the locate function, once the route is identified with the help of the substring_index function is excluded the text that I do not require and I only get the text that is relevant to me

case
    when
        locate('[:]', name) > 0
    then
        substring_index(substring_index(name, '[:es]', -1),'[:en]', 1)
    else
        substring_index(substring_index(name, '<!--:es-->', -1),'<!--:-->', 1)
end names

I add a sqlfiddle to test

This answer was useful in this case