Numeric functions in SQL are most popular in banking projects. The Popular functions are Round, Mod, and Truncated or Truncate. Below is the example SQL query.
The below real-time example gives you good idea on MOD,ROUND and TRUNC functions quickly so that you can apply in your project too.
Also read: LTRIM and RTRIM best examples
The usual ROUND() functions uses the scientific method, but you can use the MOD() function to implement the commercial method. Mostly used in all banking projects this kind of conversion.
Assume a simple list of two decimal place numbers we wish to round to one decimal place.
SQL Query to create table
CREATE TABLE Accounts (amount DECIMAL(5,2) NOT NULL); alter session set nls_numeric_characters='.,' INSERT INTO ACCOUNTS(AMOUNT) VALUES(100.20); INSERT INTO ACCOUNTS(AMOUNT) VALUES(300.20); INSERT INTO ACCOUNTS(AMOUNT) VALUES(400.30); SELECT * FROM ACCOUNTS;
Data in Table
SQL query ROUND and MOD functions
SELECT amount, ROUND(amount, 1) AS scientific, CASE WHEN MOD((100 * amount), 10) != 5 THEN ROUND(amount, 1) ELSE CASE WHEN MOD (FLOOR(MOD((10 * amount), 10)), 2) = 0 THEN TRUNC(amount, 1) -- even, down ELSE ROUND(amount, 1) -- odd, up END END AS commercial FROM Accounts;
The result is as follows