Regular expression to parse out numbers with optional precision.

This regular expression will parse out numbers with or without decimals. In this case, the number has to be surrounded by parenthesis () in order for the expression to work.

[sourcecode language=”sql”]

SELECT LTRIM(RTRIM(REGEXP_SUBSTR(‘&str’, ‘\([[:digit:]]*\.?[[:digit:]]*\)’),’)’),'(‘) RESULT FROM DUAL;

[/sourcecode]

[sourcecode]
SQL> SELECT LTRIM(RTRIM(REGEXP_SUBSTR(‘&str’, ‘\([[:digit:]]*\.?[[:digit:]]*\)’),’)’),'(‘) RESULT FROM DUAL;
Enter value for str: The groceries cost (123.45) today.
RESULT
——
123.45
1 row selected.
SQL>
[/sourcecode]