CODEWITHSUNDEEP
sqloracle-databasenumbersdecimaltruncate
cardonas
cardonas

Reputation: 109

How to extract first number after decimal point in value

I have an age column which calculates age for each member in my report.The output is a whole number followed by a decimal point and numbers. I would like the first number only right after the decimal point .

I tried trunc but it gives me everything before the decimal and then the number I want after .Then I tried to trunc with a call out with a comma and it doesnt work.

trunc(age,',')

Example -

age 15.7

expected output 7

Upvotes: 2

Views: 1902

Answers (3)

Andrei Odegov
Andrei Odegov

Reputation: 3439

Multiply by 10, trunc it and take the remainder of the division by 10.

with age as (select 15.7231 age from dual)
select mod(trunc(10*age), 10) dp from age

Output:

DP
--
 7

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21075

Here the mathematical answer

  • take the decimal part by susbriacting the whole part (trunc).

  • multiply by 10 and take the whole part

.

with age as (select 15.7231 age from dual)
select trunc(10*(age-trunc(age))) dp1  from age

       DP1
----------
         7

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

try like below

select substr(to_char(15.7,'9999.0'),-1,1) as col from dual
 it will return 7

Upvotes: 1

Related Questions