DEV Community

Scale
Scale

Posted on

GBase 8s TRUNCATE Function: Precise Numeric Truncation Without Rounding

#gbase #database

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])
Enter fullscreen mode Exit fullscreen mode


`

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)