CODEWITHSUNDEEP
sqloracle-database
Akhilesh
Akhilesh

Reputation: 1

How do i replace decimal values with a specific value oracle?

For example i have these values :

9.0001
8.1556
1.0000
9.7345
2.0003

I want my result to be-

9.5
8.5
1.0
9.5
2.5

So any decimal value in the range (between 0.0001 – 0.9999) should change to .5

Upvotes: 0

Views: 64

Answers (3)

Mudassar Zahid
Mudassar Zahid

Reputation: 305

i think you should try 

SELECT ROUND(15.193,1) ...

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522084

You can do this with only using the floor() and ceil() functions:

select floor(col) + (ceil(col) - floor(col)) / 2
from your_table

To see how this formula would work, consider the value 9.0001:

floor(9.0001) + (ceil(9.0001) - floor(9.0001)) / 2
9 + (10 - 9) / 2
9 + 1/2
9.5

which is what you expected. Also note that for 1.0000 the value would remain 1.0000 because the ceiling equals the floor in this case.

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Assuming the values are always to going to be positive, you can use this:

to_char(case trunc(x) = x then x else trunc(x) + 0.5 end, '99990D0')

if not, add a further case to do a subtraction or addition based on the sign.

Upvotes: 0

Related Questions