Formatting or fixing Decimal places
Since the beginning of coding, developer tools have all been about simplifying complex coding while cutting down development time. And the tried tested method has been creating product specific additions to standard code syntax.
While this industry practice does ease a developer’s job, it can get confusing if one is coding across multiple tools or platforms.
Yesterday, I was trying out ways to format decimal places in both Alteryx and SQL. I knew a lot of functions in SQL, but when I delved deeper, I found more functions like TO_CHAR() and TRUNC(). Since the best way to remember is to maintain a diary or notes — so here I am again with a new post.
In Alteryx, by default, all measures were Fixed Decimal and size 24.7. So, I used the Select tool. For maintaining 2 decimal places, set the size of the numeric column as 19.2. Similarly, for 3 decimal places set it as 19.3 and for 4 decimal places, 19.4. Then click the point which connects this select tool to any other tool. It has the look of a green triangle pointing to your right. In the result pane below, please ensure that you select 000 which appears on the extreme right next to Action ˅. It worked for me but I’m not sure if this will work for all scenarios.
In SQL, we can use ROUND(), CAST(), FORMAT(), CONVERT() and STR() functions to set the decimal points. Example: ROUND(342.5738, 2) //output will be 342.57
ROUND(342.5738, 2,0) //output will be 342.57
ROUND(342.5738, 2, 1) //output will be 342.57
ROUND(342.53738, 2, 2) //output will be 342.53
ROUND(342.53738, 2, 0) //output will be 342.54
ROUND(342.5378, 3, 1) //output will be 342.537
ROUND(342.5378, 3, 0) //output will be 342.538
The third part of the function is optional. It can either have 0 or 1. If other than 0 then it truncates the result to the number of decimal.
CAST(342.3738 as DECIMAL(10,2)) //output will be 342.374
CAST(235.415 as decimal(12,2)) //output will be 235.42
CAST(235.4655 as decimal(12,3)) //output will be 235.466
CAST(235.4653 as decimal(12,3)) //output will be 235.465
FORMAT(389.5678, ‘###.##’) //output will be 389.57
FORMAT(185, ‘000.00’) //output will be 185.00
FORMAT(185, ‘###.##’) //output will be 185
CONVERT(DECIMAL(5, 2), 275.846) //output will be 275.85
CONVERT(DECIMAL(5, 1), 275.846) //output will be 275.8
STR(275.849, 6, 2) //output will be 275.85 but in string
STR(275.849, 6, 1) //output will be 275.8 and will be string
In MySQL, we can use FORMAT() function
FORMAT(17654.2574, 3) //output will be 17654.257
FORMAT(200, 4) //output will be 200.0000
TRUNCATE(0.179, 2) //output will be 0.17. The number will be truncated and not rounded
ROUND(0.179, 2) //output will be 0.18 as the number will be rounded depending on the number in the third decimal place.
CAST(543.879 as DECIMAL(5, 2)) //output will be 543.88
CONVERT(77.4567, DECIMAL(5, 2)) //output will be 77.46
In Oracle, we can use TRUNC(), ROUND() and TO_CHAR() functions.
TRUNC(2365.9182, 2) //output will be 2365.91. Note TRUNC() doesn’t support rounding off so the output will not be 2365.92
ROUND() works in the same manner as that in SQL
TO_CHAR(84.7261, ‘fm99D00’) //output will be 84.73
TO_CHAR(84.72617567, ‘fm99D0000’) //output will be 84.7262
TO_CHAR(84.7261, ‘99D00’) //output will be 84.73
TO_CHAR(84.7261, ‘fm99D99’) //output will be 84. (85 followed by dot).
TO_CHAR(84.7261, ‘99D99’) //output will be 84.73
Here, the issue with the TO-CHAR function is that the output will always be in string. ‘fm’ stands for format modifier and will remove any padding that may be applied to the result. No ‘fm’ means you may end up with trailing zeros. Also, the number of 0s you add after D that is the decimal place answer you will get. I would suggest clicking this if you want to know more on this function.
In Snowflake, TRUNC(), ROUND(), CEIL(), FLOOR() functions can be used
TRUNC(123.1232, 3) //output will be 123.123
TRUNC(978.978, 2) //output will be 978.97
ROUND() works in the same manner as that in SQL. However, the difference is in data type.
ROUND(1.008, 2) //output is 1 if 1.008 is float and output will be 1.01 if 1.008 is decimal. In Float the number stored will be larger 1.00786521333 where else in decimal, it is stored as an exact number.
ROUND(7.7, 0) //output will be 8
ROUND(7.7,0,’HALF_TO_EVEN) //output will be 7
FLOOR(167.327, 1) //output will be 167.3
CIEL(167.327, 1) //output will be 167.4
CIEL(167.167) //output will be 168.
Do note, CIEL rounds off to the nearest equal or larger integer with the specified number of decimal places.
However, here I have not covered SQL, Snowflake functions like Round, Ciel and Floor that allow a negative scale i.e. negative decimal place. That’s for another post. But if you want to know then read about it here or here.
I may have missed a few but this seems sufficing. Until next time.
