Home » SQL & PL/SQL » SQL & PL/SQL » Number Conversion (Oracle 10g)
icon7.gif  Number Conversion [message #667056] Wed, 06 December 2017 03:10 Go to next message
maanosajnabi
Messages: 3
Registered: December 2017
Junior Member
Please help me in getting Valid Number from following query:

select to_number(regexp_replace('Low count (3.6) with mostly mature lymphocytes. Advised CP Again.', '[^0.0-9]', '')) from dual;

regards
Re: Number Conversion [message #667057 is a reply to message #667056] Wed, 06 December 2017 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to first validate your regexp expression:
SQL> select regexp_replace('Low count (3.6) with mostly mature lymphocytes. Advised CP Again.', '[^0.0-9]', '') from dual;
REGEX
-----
3.6..
So the error is expected.
Assuming the format is always what you posted (a single number with an integer part followed by a dot and a decimal fraction):
SQL> select regexp_substr('Low count (3.6) with mostly mature lymphocytes. Advised CP Again.','\d+\.\d+') from dual;
REG
---
3.6

[Updated on: Wed, 06 December 2017 04:58]

Report message to a moderator

Re: Number Conversion [message #667061 is a reply to message #667057] Wed, 06 December 2017 03:58 Go to previous message
maanosajnabi
Messages: 3
Registered: December 2017
Junior Member
Thanks. Resolved.
Previous Topic: Term Match with Jaro-Winkler logic
Next Topic: Help in Query for Required output
Goto Forum:
  


Current Time: Fri Mar 29 03:21:50 CDT 2024