Home » SQL & PL/SQL » SQL & PL/SQL » What function to use to round character? (Oracle 10g)
What function to use to round character? [message #314792] Thu, 17 April 2008 15:33 Go to next message
cores
Messages: 15
Registered: March 2008
Junior Member
Hi,

I have a column, data type - NVARCHAR2(500 CHAR),
The value can be like 2272354.000004

I need to 'round' it by taking all the characters before the period and convert to number.

I tried:
select TO_NUMBER(substr (lookup_values.CONSTANT, 1, instr (lookup_values.CONSTANT, '.') - 1)) original from projects;

Does anybody knows better way to make it?

Thank you
Re: What function to use to round character? [message #314799 is a reply to message #314792] Thu, 17 April 2008 15:56 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Regular expressions?
SQL> SELECT REGEXP_SUBSTR('2272354.000004', '[[:digit:]]+') FROM dual;

REGEXP_
-------
2272354

SQL>
Re: What function to use to round character? [message #314844 is a reply to message #314792] Fri, 18 April 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does anybody knows better way to make it?

Better in what?

Regards
Michel
Re: What function to use to round character? [message #314860 is a reply to message #314844] Fri, 18 April 2008 01:22 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Space consuming? Hm, I didn't think of that. I should have used smaller font, like this:
SQL> SELECT REGEXP_SUBSTR('2272354.000004', '[[:digit:]]+') FROM dual;

REGEXP_
-------
2272354

SQL>
Re: What function to use to round character? [message #314865 is a reply to message #314860] Fri, 18 April 2008 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hard for my eyes but Laughing

Regards
Michel
Re: What function to use to round character? [message #314869 is a reply to message #314865] Fri, 18 April 2008 01:37 Go to previous messageGo to next message
gopu_g
Messages: 54
Registered: March 2008
Location: mumbai
Member

I think now you can see Smile Smile


SQL> SELECT REGEXP_SUBSTR('2272354.000004', '[[:digit:]]+') FROM dual;

REGEXP_
-------
2272354

SQL>
Re: What function to use to round character? [message #314990 is a reply to message #314869] Fri, 18 April 2008 08:26 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
What did this add to Littlefoot's answer?
Re: What function to use to round character? [message #315004 is a reply to message #314844] Fri, 18 April 2008 09:37 Go to previous message
cores
Messages: 15
Registered: March 2008
Junior Member
Thanks to all for responses.

Better way - I meant less formulas, simpler way.


Thanks again.
Previous Topic: Problem with FULL OUTER JOIN
Next Topic: Is "a" a keyword in oracle
Goto Forum:
  


Current Time: Sun Dec 04 15:01:44 CST 2016

Total time taken to generate the page: 0.09640 seconds