How to remove non-numeric characters (except full stop "." ) from a string in amazon redshift

Question

I have been trying to figure out how to remove multiple non-numeric characters except full stop ("."), or return only the numeric characters with full stop (".") from a string. I've tried:

SELECT regexp_replace('~�$$$1$$#1633,123.60&&!!__!', '[^0-9]+', '')

This query returns following result : 1163312360

But I want the result as 11633123.60

Answer 1

Please try this:

The below regex_replace expression will replace all character which are not ("^") in the (range of 0-9) & "."

SELECT regexp_replace('ABC$$$%%11633123.60','([^0-9.])','') FROM DUAL;

It returns the expected output "11633123.60"