oracle regular expression to extract decimal numbers from string

Question

I am trying to extract decimal numbers from a random string. How to do this using regex_replace function? Optionally i want to retain negative sign for negative integers.

example:

input:

'text abc023.46'
 '-123.12abc'

output:

023.46
-123.12

this is not working...select REGEXP_REPLACE('text abc123.46','[+-]?\d+(\.\d+)?','') from dual gives output as text abc — user1751356, May 01 '20 at 13:25

score 0 · Accepted Answer · answered May 01 '20 at 13:15

0

Use REGEXP_SUBSTR:

SELECT REGEXP_SUBSTR(col, '-?\d+(\.\d+)?')
FROM yourTable;

This pattern also makes the decimal component optional.

answered May 01 '20 at 13:15

Tim Biegeleisen

387,723
20
200
263

1

its working as expected. Thank you. Could you please explain the pattern you used. – user1751356 May 01 '20 at 13:28

oracle regular expression to extract decimal numbers from string

1 Answers1