Home » SQL & PL/SQL » SQL & PL/SQL » How to extract the left and right sides of the decimal
How to extract the left and right sides of the decimal [message #379004] Sat, 03 January 2009 21:18 Go to next message
chase53
Messages: 6
Registered: January 2009
Junior Member


How to extract the left and right hand sides of the decimal point and save these in a view with two columns....!!!

like from 23.45 we shud separate 23 and 45 and put them in a new view..!!!

Thanks in advance
Re: How to extract the left and right sides of the decimal [message #379006 is a reply to message #379004] Sat, 03 January 2009 21:46 Go to previous messageGo to next message
rajput.anshu
Messages: 45
Registered: October 2008
Location: Bangalore
Member
Suppose this is a table A data column name AA
23.45

Create or Replace View Deci
as select substr(AA,1,2),substr(instr(AA,'.')+1,1) from A
Try it........Is it correct or not?
I didn't check it...
icon14.gif  Re: How to extract the left and right sides of the decimal [message #379009 is a reply to message #379006] Sat, 03 January 2009 22:02 Go to previous messageGo to next message
chase53
Messages: 6
Registered: January 2009
Junior Member
Thanks very much...!!!

It worked...!!!
Re: How to extract the left and right sides of the decimal [message #379010 is a reply to message #379004] Sat, 03 January 2009 22:04 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

NO.. Its not correct .

It may work for 23.45 But what if Number is 102.345 ?

Anyway try with Trunc and Mod Functions

Smile
Rajuvan.

Re: How to extract the left and right sides of the decimal [message #379014 is a reply to message #379004] Sat, 03 January 2009 22:22 Go to previous messageGo to next message
chase53
Messages: 6
Registered: January 2009
Junior Member
yeah you are right.....I altered it anyway.

I thanked him for giving some idea....!!!

your concern is appreciated too....!!!

Thanks
Re: How to extract the left and right sides of the decimal [message #379037 is a reply to message #379014] Sun, 04 January 2009 02:57 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yet another, regular expressions based way to do the job:
SQL> WITH TEST AS
  2    (SELECT 123.45 col FROM dual
  3     UNION
  4     SELECT 1.2345 FROM dual
  5     UNION
  6     SELECT 1234.2 FROM dual
  7    )
  8  SELECT col,
  9    REGEXP_SUBSTR(col, '[[:digit:]]*') left,
 10    REGEXP_SUBSTR(col, '[[:digit:]]*$') right
 11  FROM TEST;

       COL LEFT       RIGHT
---------- ---------- ----------
    1.2345 1          2345
    123.45 123        45
    1234.2 1234       2

SQL>
Re: How to extract the left and right sides of the decimal [message #379041 is a reply to message #379037] Sun, 04 January 2009 03:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Littlefoot wrote on Sun, 04 January 2009 09:57
Yet another, regular expressions based way to do the job

I think the idea behind this one is as wrong as rajput.anshu's solution. Both treat the input as string-values, whereas it is quite clear that we are dealing with numbers.
Use numeric functions on numbers and string-functions on strings.

Just my opinion, of course..
Re: How to extract the left and right sides of the decimal [message #379043 is a reply to message #379037] Sun, 04 January 2009 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but why converting numbers to strings to apply regular expression and then reconverting them to numbers when number functions apply?

Regards
Michel

[Edit: Frank said the same thing, sorry, this is the drawback of opening 10 windows at the same time]

[Updated on: Sun, 04 January 2009 03:17]

Report message to a moderator

Re: How to extract the left and right sides of the decimal [message #379100 is a reply to message #379043] Sun, 04 January 2009 19:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Is there a way to remove the decimal point without a character conversion (eg. TO_CHAR with 'V' format model, TRANSLATE / REPLACE)?

I'm not suggesting that REs are an optimal solution, but TRUNC/MOD is not the end of the story. Or is it?

Ross Leishman
Re: How to extract the left and right sides of the decimal [message #379103 is a reply to message #379004] Sun, 04 January 2009 21:22 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I think , TRUNC and MOD is an end of the story .

SQL> WITH TEST AS
  2       (SELECT 123.45 col FROM dual
  3         UNION
  4         SELECT 1.789 FROM dual
  5         UNION
  6         SELECT 1234.2 FROM dual
  7        )
  8   SELECT col, TRUNC(col)leftn, Mod(col,1)  rightn from test;

       COL      LEFTN     RIGHTN
---------- ---------- ----------
     1.789          1       .789
    123.45        123        .45
    1234.2       1234         .2

SQL>


Isn't the same the OP was asking for ?

Smile
Rajuvan.

[Updated on: Sun, 04 January 2009 21:23]

Report message to a moderator

Re: How to extract the left and right sides of the decimal [message #379105 is a reply to message #379103] Sun, 04 January 2009 22:57 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
thanks raju for understanding the problem and as well ending in the positive note. Smile
Re: How to extract the left and right sides of the decimal [message #379137 is a reply to message #379100] Mon, 05 January 2009 01:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rleishman wrote on Mon, 05 January 2009 02:55
Is there a way to remove the decimal point without a character conversion (eg. TO_CHAR with 'V' format model, TRANSLATE / REPLACE)?

I'm not suggesting that REs are an optimal solution, but TRUNC/MOD is not the end of the story. Or is it?

Ross Leishman

Removing the decimal point, without any consideration on the number of decimals (ie you don't care how many decimals you started with, means (in my opinion) that you treat the "number" as a string anyway.
The original question keeps both sides of the decimal separator as separate numbers, making it a number-operation.
Re: How to extract the left and right sides of the decimal [message #405746 is a reply to message #379137] Fri, 29 May 2009 05:24 Go to previous message
raf.der
Messages: 1
Registered: May 2009
Junior Member
try whith this formula:

select 123.456,TRUNC(123.456),
(substr (123.456-TRUNC(123.456),2)) from dual


Raffaele
Previous Topic: How to stop a running job
Next Topic: when no_data_found
Goto Forum:
  


Current Time: Sat Dec 03 00:52:02 CST 2016

Total time taken to generate the page: 0.15966 seconds