Insert decimal separator in SQL result

Luis Fernando Zanuncio 0
Viewed 43

I'm using MySql. I need to convert the double 2783571287 result in the decimal '27835712,87'. In my case, I'm using ',' as decimal separator.

I managed to use the cast function, but I can't previously set the two decimal places that make up the end of the value.

Also, I was not able to "cut" the value using the SUBSTRING function as the amount of characters may vary.

Does someone know how I can do it?

Thanks

mysql sql
1 Answers

Also, I was not able to "cut" the value using the SUBSTR function as the amount of characters may vary.

If you want to drop the last two characters, use substr() with length() as

mysql> select substr(2783571287, 1, length(2783571287) - 2);
+-----------------------------------------------+
| substr(2783571287, 1, length(2783571287) - 2) |
+-----------------------------------------------+
| 27835712                                      |
+-----------------------------------------------+
1 row in set (0.00 sec)

You can also extract the last two characters by using right()

mysql> select right(2783571287, 2);
+----------------------+
| right(2783571287, 2) |
+----------------------+
| 87                   |
+----------------------+
1 row in set (0.00 sec)

Then

mysql> select concat(substr(2783571287, 1, length(2783571287) - 2), ',', right(2783571287, 2));
+----------------------------------------------------------------------------------+
| concat(substr(2783571287, 1, length(2783571287) - 2), ',', right(2783571287, 2)) |
+----------------------------------------------------------------------------------+
| 27835712,87                                                                      |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Yoichi Nakayama0
Related