SQL Numeric Functions Easy Ways to Use

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

AMOUNT
100.2
300.2
400.3

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;

Round function