# Ensure percentages are between 0 and 1, inclusive (using a single function)

I have percentages in a `condition` table:

``````create table condition (percent_decimal number(3,2));
insert into condition values (-0.01);
insert into condition values (0.1);
insert into condition values (1);
insert into condition values (1.1);
commit;

PERCENT_DECIMAL
---------------
-0.01
.1
1
1.1
``````

I want to select the values, but modify them to present them as percentages between 0 and 1 (inclusive):

• Convert `-0.01` to `0`
• Leave `.1` as is
• Leave `1` as is
• Convert `1.1` to `1`

I can successfully do this using the greatest and least functions:

``````select
percent_decimal,
least(1,greatest(0,percent_decimal)) as percent_modified
from
condition

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
-0.01                0
.1               .1
1                1
1.1                1
``````

However, I'm wondering if there is a more succinct way of doing this--with a single function.

I'm often pleasantly surprised by the clever techniques that DBA-SE members come up with. When I think I've come up with the simplest way to do something, someone comes up with something even simpler.

• Oracle does allow you to define your own functions. Feb 13 '18 at 19:34

Ok, this can't be a serious question, reminds me of https://codegolf.stackexchange.com/

I can do better, no function at all.

``````select
percent_decimal,
case
when percent_decimal <= 0 then 0
when percent_decimal >= 1 then 1
else percent_decimal
end as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
-0.01                0
.1               .1
1                1
1.1                1
``````

But we need a function. Finally Oracle 12.2 allows long (128 bytes) identifiers, so we can give more meaningful names:

``````with function
"Convert -0.01 to 0, Leave .1 as is, Leave 1 as is, Convert 1.1 to 1" (p number)
return number
as
begin
if (p = -0.01) then return 0; end if;
if (p = 1.1) then return 1; end if;
return p;
end;
select
percent_decimal,
"Convert -0.01 to 0, Leave .1 as is, Leave 1 as is, Convert 1.1 to 1"(percent_decimal)
as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
-0.01                0
.1               .1
1                1
1.1                1
``````

I also happen to have a super secret function that coincidentally does exactly what you need:

``````create or replace function "." wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
97 aa
30RI5qP/Li6VhppxgO2AXQw62mYwg1xfrZ6pfI6iO8cYr/L9s3tguDonZ52omRm1G6sfUCen
xkcEU/4jCxrp7Ii4VNrCrVY6cDr0UNz1Id5vZ5p7e0S0F+AEd55sz9JyGokPaBbDJHFVdW+A
IAHg6Xpxmglu3BtbDtBmCA==

/
``````
``````select
percent_decimal,
"."(percent_decimal) as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
-0.01                0
.1               .1
1                1
1.1                1
``````

Must use a built-in function? No problem.

``````select
percent_decimal,
round(percent_decimal/1.05, 1) as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
-0.01                0
.1               .1
1                1
1.1                1
``````