Home » SQL & PL/SQL » SQL & PL/SQL » separate decimal point
separate decimal point [message #347259] Thu, 11 September 2008 03:39 Go to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
Hi
I would like to separate decimal point in the records.

For example I got a number 5.91371.11.00.100037
and I need to know what is after second decimal point, or third decimal point (in this example 11.00.100037).

Is it a possible to make it by use of any oracle function?

thanks a lot
Re: separate decimal point [message #347264 is a reply to message #347259] Thu, 11 September 2008 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
For example I got a number 5.91371.11.00.100037

This is not a number, just a string, a number can't have several decimal points.

SUBSTR/INSTR

Regards
Michel
Re: separate decimal point [message #347270 is a reply to message #347259] Thu, 11 September 2008 03:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
5.91371.11.00.100037 is not a number - it's a string
Numbers don't have more than one decimal point in them.

If I were you' I'd look at REGEXP_SUBSTR
The regular expression [^.]+ will match a string of characters up to a .
Re: separate decimal point [message #347272 is a reply to message #347259] Thu, 11 September 2008 04:07 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member

you right, its string not number of course.
Reg: Separate decimal point [message #347793 is a reply to message #347259] Sun, 14 September 2008 00:37 Go to previous message
senthilrajan
Messages: 1
Registered: September 2008
Location: Bahrain
Junior Member

Hi,

Use this following Sql for the above:

Select Substr(:text, Instr(:text,'.',1,2)+1) from dual

2 represents position of the Decimal point (2nd), if you want to retrieve it from 3rd position give 3.

Regards,

Senthil K
Previous Topic: SQL
Next Topic: WHEN OTHERS Exception
Goto Forum:
  


Current Time: Sat Dec 10 13:03:04 CST 2016

Total time taken to generate the page: 0.04485 seconds