0

I have a function that returns the age of a person or 'Deceased' if they have died. Because it returns either a number or Deceased the RETURN is a VARCHAR2.

When an age is returned it returns a value like: 72.93541936015413085912743681395289882628. Is there anyway to alter the value within the variable to only return the charaters before the decimal point?

Thanks

ToniHopkins
  • 369
  • 3
  • 11
  • 30
  • You could use a combination of SUBSTR() and INSTR() as explained here: http://stackoverflow.com/a/4389623/1606729 – koopajah Dec 07 '12 at 16:17

1 Answers1

2

cast it as a number:

floor(cast(column as float))

will round it down

ceil(cast(column as float))

will round it up

round(cast(column as float))

will round it

and if you had to send it back to as a string, cast it back

 cast(round(cast(column as float)) as varchar2(50))

but you might need to check for the value 'deceased' , so use a case:

select retVal =
case column
 when 'deceased' then 'deceased' -- pushing up daisies
 else cast(round(cast(column as float)) as varchar2(50)) --alive and kicking
end
jenson-button-event
  • 18,101
  • 11
  • 89
  • 155
  • @koopajah It will work: Oracle will try to cast the string to a number, which `round`, `floor` or whatever will operate on and, when it's put back into a `varchar2`, Oracle will cast it back. There just needs to be some logic to get around the "Deceased" value. – Xophmeister Dec 07 '12 at 16:21
  • Thanks for your help. For some reason I couldnt get it working using the cast however using your ideas for the rest I solved it with this: SELECT to_number(age) INTO v_number from dual; SELECT floor(v_number) into age from dual; – ToniHopkins Dec 10 '12 at 09:15