Home » SQL & PL/SQL » SQL & PL/SQL » Separate Integer and decimal from a number datatype column (9i)
icon5.gif  Separate Integer and decimal from a number datatype column [message #406040] Mon, 01 June 2009 22:23 Go to next message
sonu_2280
Messages: 3
Registered: June 2009
Location: USA
Junior Member
Hi All,

I have a question on separating the integer and decimal part from a NUMBER(datatype) column. I have a column with NUMBER(datatype) say for example 34.56 in my table xx, I need to separate the integer and decimal part of it and write to 2 different columns(34 to one column and .56 to another column). Although this looks simple, I am not quite sure on how to do it. I tried instr(to_char)...converting them to Char etc..using combination of functions and commands..but data is so huge and it could be any number (say even 7877654.56 ). How do I do this , I also tried using regexp_substr commands, but I am not sure about this one too.
I am using oracle 9i. Could anyone please suggest a way to handle this??

Thanks so much for your help in advance!!!!
Re: Separate Integer and decimal from a number datatype column [message #406043 is a reply to message #406040] Mon, 01 June 2009 22:27 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
TRUNC(x)
x - TRUNC(x)


Ross Leishman
Re: Separate Integer and decimal from a number datatype column [message #406045 is a reply to message #406040] Mon, 01 June 2009 22:36 Go to previous messageGo to next message
sonu_2280
Messages: 3
Registered: June 2009
Location: USA
Junior Member
Great!!! perfect .......Thank you very much!!!!
Re: Separate Integer and decimal from a number datatype column [message #406088 is a reply to message #406045] Tue, 02 June 2009 03:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd use FLOOR rather than TRUNC, but that really is just personal preference.
Re: Separate Integer and decimal from a number datatype column [message #406093 is a reply to message #406088] Tue, 02 June 2009 03:31 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello JRowbottom sir,
Quote:
I'd use FLOOR rather than TRUNC, but that really is just personal preference.


May I know the reason?

regards,
Delna
Re: Separate Integer and decimal from a number datatype column [message #406095 is a reply to message #406093] Tue, 02 June 2009 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I do not agree with JRowbottom:
SQL> with 
  2    data as (
  3      select 1.1 val from dual
  4      union all
  5      select -1.1 val from dual
  6    )
  7  select val, trunc(val) trc, floor(val) flr 
  8  from data
  9  /
       VAL        TRC        FLR
---------- ---------- ----------
       1.1          1          1
      -1.1         -1         -2

2 rows selected.

Regards
Michel
Re: Separate Integer and decimal from a number datatype column [message #406101 is a reply to message #406095] Tue, 02 June 2009 04:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good spot Michel - I always used to use them on positive quantities, so I hadn't really thought of that.

Consider my advice withdrawn.

Re: Separate Integer and decimal from a number datatype column [message #406134 is a reply to message #406040] Tue, 02 June 2009 07:44 Go to previous message
sonu_2280
Messages: 3
Registered: June 2009
Location: USA
Junior Member
Thank you all for your time and help!!!!
Previous Topic: Problem with BLOB data
Next Topic: SQL Server code required based on Oracle
Goto Forum:
  


Current Time: Sun Feb 16 00:44:08 CST 2025