In many data processing scenarios, precision matters more than approximation.
While functions like ROUND perform rounding, sometimes we need strict numeric truncationโcutting off digits without any rounding logic.
In GBase 8s database, the TRUNCATE function is designed exactly for this purpose.
๐ 1. What is TRUNCATE in GBase 8s?
The TRUNCATE function performs exact numeric truncation, meaning:
- It cuts off digits beyond a specified precision
- It does NOT apply rounding
- It ensures deterministic numeric output
๐ This makes it ideal for:
- Financial calculations
- Scientific data processing
- Reporting systems
- Precision-sensitive analytics
โ๏ธ 2. Syntax of TRUNCATE
TRUNCATE(n [, m])
`
Parameters:
- n โ The numeric value to be truncated
- m โ Number of digits to keep
Behavior of m:
| Value of m | Meaning |
|---|---|
| m > 0 | Truncate to m decimal places |
| m = 0 | Truncate to integer |
| m < 0 | Truncate to left of decimal point |
๐ 3. Basic Example
Truncate to 2 decimal places
sql id="gbase_truncate_01"
SELECT TRUNCATE(1234.234, 2) FROM dual;
Result:
plaintext
1234.23
๐ Notice: No rounding occurs (unlike ROUND function)
๐ข 4. Integer-Level Truncation
Example: Truncate to integer
sql id="gbase_truncate_02"
SELECT TRUNCATE(1234.234, 0) FROM dual;
Result:
plaintext
1234
๐ 5. Left-Side Truncation (Negative m)
Example:
sql id="gbase_truncate_03"
SELECT TRUNCATE(1234.234, -2) FROM dual;
Result:
plaintext
1200
๐ Removes digits to the left of the decimal point
โ ๏ธ 6. Special Cases
NULL Input
sql
SELECT TRUNCATE(NULL, 2) FROM dual;
๐ Returns NULL
Empty String
sql
SELECT TRUNCATE('', 2) FROM dual;
๐ Returns NULL
Overflow Decimal Precision
If m exceeds available decimal places:
- Result is padded with zeros
Example:
sql id="gbase_truncate_04"
SELECT TRUNCATE(12.3, 4) FROM dual;
Result:
plaintext
12.3000
๐ง 7. TRUNCATE vs ROUND
| Function | Behavior |
|---|---|
| ROUND | Rounds values |
| TRUNCATE | Cuts off values |
Example Comparison:
sql id="gbase_compare_01"
SELECT ROUND(1234.235, 2), TRUNCATE(1234.235, 2) FROM dual;
Result:
plaintext
ROUND โ 1234.24
TRUNCATE โ 1234.23
โก 8. Real-World Use Cases
โ Financial Systems
- Prevent rounding errors in accounting
- Ensure strict monetary precision
โ Scientific Calculations
- Maintain controlled precision levels
- Avoid propagation of rounding errors
โ Reporting Systems
- Standardize numeric display formats
- Ensure consistent output across reports
๐งฉ 9. Why TRUNCATE Matters in Databases
In real systems:
- Rounding can introduce bias
- Small precision errors accumulate
- Deterministic results are critical
๐ TRUNCATE ensures predictable numeric behavior
๐ Final Thoughts
The TRUNCATE function in GBase 8s database is a simple but powerful tool for:
- Precision control
- Deterministic numeric processing
- Clean data output formatting
Unlike rounding functions, it provides strict numeric control without approximation.
Top comments (0)