0

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
James Z
  • 12,209
  • 10
  • 24
  • 44
user1751356
  • 565
  • 4
  • 14
  • 33
  • 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

1 Answers1

0

Use REGEXP_SUBSTR:

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

This pattern also makes the decimal component optional.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360