Over a million developers have joined DZone. {{announcement.body}}
{{announcement.title}}

DZone's Guide to

# Simulation of TRUNC() in Derby

· Java Zone ·
Free Resource

Comment (1)

Save
{{ articles.views | formatCount}} Views

Get the Edge with a Professional Java IDE. 30-day free trial.

Derby is missing out a lot of functions from the set of functions that other databases usually provide. One example is the TRUNC(value, decimals) function. According to the Wikipedia, truncation can be achieved as such:

```-- trunc(x, n)
CASE WHEN x > 0
THEN
floor(power(10, n) * x) / power(10, n)
ELSE
ceil(power(10, n) * x) / power(10, n)
END
```

Unfortunately, there is no POWER(base, exponent) function in Derby either. But no problem, we can simulate that as well. Let’s consider the Wikipedia again and we’ll find:

`power(b, x) = exp(x * ln(b))`

If we substitute that into the original simulation, we get for Derby:

```-- trunc(x, n)
CASE WHEN x > 0
THEN
floor(exp(n * ln(10)) * x) / exp(n * ln(10))
ELSE
ceil(exp(n * ln(10)) * x) / exp(n * ln(10))
END
```

Verbose, probably quite inefficient, but effective! Let’s run a short test, too:

```create table test (x numeric(10, 5), n int);

insert into test values (11.111, 0);
insert into test values (11.111, 1);
insert into test values (11.111, 2);
insert into test values (11.111, -1);

select
x, n,
case when x >= 0
then
floor(exp(n * ln(10)) * x) / exp(n * ln(10))
else
ceil(exp(n * ln(10)) * x) / exp(n * ln(10))
end "trunc(x, n)"
from test;
```

The above yields

X N TRUNC(X, N)
11.111 0 11
11.111 1 11.1
11.111 2 11.11
11.111 -1 10

Get the Java IDE that understands code & makes developing enjoyable. Level up your code with IntelliJ IDEA. Download the free trial.

Topics:

Comment (1)

Save
{{ articles.views | formatCount}} Views

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

# {{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}