Add to MYSQL query in varchar field by deleting Strings

Asked

Viewed 400 times

0

How to add directly to the query MYSQL when the field is sweep and need to delete some strings.

Example:

+------------+
|valor_total |
+------------+ 
|R$ 2.277,90 | 
|R$ 3.217,30 | 
|R$ 9.857,40 | 
+------------+

The attempt below returns me sum = 0

SELECT REPLACE(valor_total, 'R$', ''), SUM(valor_total) AS TotalDaSoma FROM carrinho WHERE representante_id='92'
mysql mysqli sum

  • My Lady, "what a field" bug! Why don’t you use a decimal at least, and does formatting by the application or in the query itself? That’s a mess.

  • I did not ask for logical or aesthetic opinion and yes solution to question , even so thank you !

  • So you don’t understand the concept of the OS. If you just want to play question and have the answer ready, you’re doing it wrong. But good luck there! ✌

  • You could make your criticism by presenting an answer! It’s not often that a client wants to update their algorithm and that’s the case, where I need to solve the problem with these features. What you said is the obvious, I think you who does not understand the concept of the platform.

  • Already have 2 answers, very good. I put in the comment exactly to add. What I said is obvious, but since there’s no reason to use it that way, there’s no way I can guess. And if I do not understand the concept, then who created this field understands of what? ... What a complication for a simple tip right!?

  • Yes let’s not argue, everyone has their understandings ! I understand what you meant too, but in the end everyone is satisfied, with each other answers with scores ! I think it’s important for people to participate!

  • 1

    Absolutely! The idea was to help, if the way to talk sounded aggressive, I apologize! And in need, we are there! ✌

  • Yes, if you need us !

Show 3 more comments

2 answers

2


You need to add the converted value:

  • remove the R$,
  • remove the point of thousands
  • replace the comma by the period

After that convert to number and add:

SELECT SUM(
cast(
  REPLACE(
    REPLACE(
       REPLACE(valor_total, 'R$ ', '') /* REMOVE R$*/
      , '.',''),   /* REMOVE O PONTO */
     ',','.')      /* TROCA A VÍRGULA POR PONTO */
        as decimal(8,2))
)  AS TotalDaSoma
FROM carrinho 
WHERE representante_id='92'

I broke into lines to facilitate understanding.

Here a fiddle demonstrating the conversion working: http://sqlfiddle.com/#! 9/5d2ca4/4

  • Perfect, thank you !

1

It is necessary to chain the functions, leaving the one that must be executed first inside the one that must be executed last, in your case would be more or less like this:

SELECT SUM(REPLACE(REPLACE(REPLACE(REPLACE(valor_total, '.', ''), ',', '.'), ' ', ''), 'R$', '')) AS TotalDaSoma
FROM carrinho
WHERE representante_id='92'

In this way the following functions will be carried out:

  • Replace . for nada;
  • Replace , for .;
  • Replace espaço for nada;
  • Replace R$ for nada;

Click here to see an example working.

  • Thanks to yours also works, I chose the other because it already converts to decimal !

Browser other questions tagged mysql mysqli sum

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