Processing of returned values in the query

Asked

Viewed 178 times

0

I need to "handle" the return of a SELECT as follows: - value saved to the bank: www.dominio.com.br or www.dominio.com - value treated on page: dominio;

I mean, I need query "remove" what is before and after the domain name.

It is quite simple to take one of the parts (before and after), but the two knots, how to do this in the query?

mysql query

  • If you want to use "editing" directly in SQL, you can try the following: SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(seu_campo, '.', 2), '.', -1) as dominio FROM sua_tabela, only replace the suggestive names by their values. As you have not provided more details of the language used, there is no other way to provide.

  • Opa, exactly what I need, the "seu_campo" I will exchange here for ORIGEM_PESSOA, except that DOMINIO is a field of the table and can have N values (dominioX, dominioZ, etc.). How can I do it? Thanks @Rafaelwithoeft

  • The as dominio is only an alias, can be replaced by the name you wish... was that what was in doubt? The field ORIGEM_PESSOA is the one described in your question that has the value www.dominio.com.br ?

  • SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(origem_pessoa, '.', 2), '.', -1) FROM person, that’s how it got what I need, this way it’s only returning the domain without www, . with or with. Thanks!! @Rafaelwithoeft

  • So it worked right? :)

  • Yes! Solved thank you very much (by the way I mark SOLVED here on Stack?)

  • Only in answers... can I assemble one for you

  • Please provided that you (or whoever helped) is "decorated" heheh

Show 3 more comments

1 answer

2


If you want to use "formatting" directly on SQL, you can try the following:

 SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(seu_campo, '.', 2), '.', -1) as dominio FROM sua_tabela

Just replace the suggestive names with their values. As you have not provided more details of the language used, there is no other way to provide.

The use of SUBSTRING_INDEX is quite simple. You provide a string, delimiter and quantity(Count).

In your case, let’s take as a basis the value www.dominio.com.br, follow the examples:

SUBSTRING_INDEX('www.dominio.com.br', '.', 2) => 'www.dominio';
//Números positivos, tudo o que estiver a esquerda do "delimitador final" (contando da esquerda) é retornado.

SUBSTRING_INDEX('www.dominio.com.br', '.', -2) => 'com.br';
//Números negativos, tudo o que estiver a direita do "delimitador final" (contando da direita) é retornado

Having the above results, we will apply your case:

SUBSTRING_INDEX(SUBSTRING_INDEX('www.dominio.com.br', '.', 2), '.', -1) => 'dominio';

As we know the first expression to be executed will return www.dominio, we use the expression again and take the first position on the right (for this we use the negative value -1), which in the case returns dominio.

I hope I have been clear in the explanation. Any question or suggestion for improvement to the answer, please feel free to request.

Sources/References:

Split value from one field to two
Documentation

Browser other questions tagged mysql query

You are not signed in. Login or sign up in order to post.