REGEX capture in SELECT statement

Databases
1

I have a special case of capturing by regular expression in the SELECT statement.
This is the background: In a poorly designed database, a user might have stored the
amount as the following record:

amount (varchar)

$7,232.05
7,232.05$
7,232.05 only
7,232.05
7232.05

I would like to capture the data with the digits and a period only.
And all the fields should be having same output in this case as:

amount (numeric/decimal/float)

7232.05
7232.05
7232.05
7232.05
7232.05

My ideal query would be something like:

SELECT
	amount REGEXP '/[\\d\\.]+/is' AS amount
FROM transactions;

What can be the real alternative to this problem?
It is not a matching for a condition, but the data extraction:
extracting the digits and a period.

By this way, I can even sort the records numerically (low to high, or high to low) with results as expected.

Here were some other issues on regular expressions, a bit different than mine:

Thank you for your time in searching for a solution.

2

Further, CAST is not a solution.
I don’t want to use PHP calculations, because I need to sort the data at the query level.

3

since mysql’s regexp can find strings but cannot replace them, the best you can do in the query is…

ORDER
    BY CAST(
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(amount,'$','')
                     ,',','') 
                     ,'.','') 
                     ,' ','') 
                     ,'only','') 
           AS UNSIGNED)
4

Poor database design –> poor solution. I really agree with you!
This was one of the real life case I have ever seen.

Even something like this in my mind:
Build all possible non-numerical records in each rows.
DISTINCT them in a temp zone.
Build the list of REPLACE dynamically.
Build the full SQL.

Just because, you cannot predict what could have been stored in the VARCHAR column.
Sometimes, we have to do the wrong way though we know it.

5

Do you have the option to convert this poor design to a better one or is it one of those “do not change anything, but please make it work” projects? I would promptly upgrade this varchar to NUMERIC storage (I’m not saying I’d just change the data type of the column of course, but do a proper re-import of the data).

6

No rights to modify the data/database.
Option to re-import really helps; but not allowed.