Convert a positive decimal number into negative

Asked

Viewed 8,376 times

5

I have the following query:

Valor1 = '200.000,00';

select Valor1 from TB1

Is there any function that converts a positive number to negative?

mysql sql mathematics

  • Do you want me to change all these values to direct negative in the table? Or do you just want to take the values? What is Valor1 = '200.000,00'; This doesn’t seem to make sense. It’s a string same? Because it is string? This seems to be a monetary value that needs to make calculations, it should not be string.

  • 1

    I made this assignment Valor1 = '200.000,00' to be more didactic to my doubt. Regarding the type of data: That same bigown this type of data should be a monetary type (decimal), but that’s how it is today in the database and I’ve got the system like this. In this table this column stores the Values (string type) and I want to select and convert this string into a negatic monetary number.

  • Take a look at [tour]. You can accept an answer if it solved your problem. You can vote on every post on the site as well. Did any help you more? You need something to be improved?

2 answers

8


If you just want to select the values as negative it’s like this:

select -CAST(REPLACE(REPLACE(Valor1, '.', ''), ',', '.') AS DECIMAL(10, 2)) from TB1

or

select -1 * REPLACE(REPLACE(Valor1, '.', ''), ',', '.') from TB1

If you want to change them on the table:

update TB1 set Valor1 = -1 * REPLACE(REPLACE(Valor1, '.', ''), ',', '.')

I put in the Github for future reference.

Pure mathematics, except for conversion. It would be good to rethink whether to use a type that requires conversion. Look at the mess you will have to make in all queries. It is better to start fixing these things to avoid future problems.

  • select -CAST('50.000,00' AS DECIMAL(10,2)) as Valor The above query returns me -50.00 I need him to call me back 50.000,00... suggestion ??

  • I got better. I think it’s okay now.

  • @Adrianocorder happened something to get acceptance?

  • A doubt, it would not be better OP treat as string and do the type conversion to application, or talking nonsense?

  • 1

    @Diegofelipe depends on his need. It is a possibility yes. His question does not speak in PHP, nor do I know if he uses this. In fact, it is best to tidy up the database structure. Of course this may have implications on the application.

0

Multiply by -1 that the result of the multiplication is the negative number.

select Valor1 * -1 AS Valor1 from TB1

Browser other questions tagged mysql sql mathematics

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