CODEWITHSUNDEEP
sqlstringamazon-web-servicesamazon-redshift
Jhansi N
Jhansi N

Reputation: 43

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

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

Upvotes: 4

Views: 16744

Answers (1)

Jayesh Mulwani
Jayesh Mulwani

Reputation: 665

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"

Upvotes: 12

Related Questions