Handling of values returned in the query

0

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

That is, I need to have query "remove" before and after the domain name.

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

    
asked by anonymous 07.04.2015 / 19:55

1 answer

2

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

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

Only replace suggestive names with their values. Since you did not provide more details of the language used, there is no way to give it another way.

Using SUBSTRING_INDEX is quite simple. You provide a string, a delimiter, and the count.

In your case, let's take the www.dominio.com.br as the base, 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, let's apply your case:

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

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

I hope I was clear in the explanation. Any questions or suggestions for improvement for the answer, feel free to ask.

Sources / References:

Split value from one field to two
< a href="https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index"> Documentation

    
07.04.2015 / 20:27