Search code examples
mysqldoublevarchar

Mysql - Remove text from INT type column

i am doing a migration

i have a column name amount_in_rmb VARCHAR. but i want to change it to DOUBLE(10,2).

The problem is, some rows of amount_in_rmb contain data like RMB1525, RM23523 and etc

I want only the 1525 but dont want the RMB because i am changing the column type to double

my question is, how to use mysql query or any method to remove the "RMB" or "RM" from the rows ? The numeric value is very important and it must not be deleted.

Solution

  • If those are the only possible prefixes, you can do:

    UPDATE YourTable
SET amount_in_rmb = CASE WHEN amount_in_rmb LIKE 'RMB%' THEN SUBSTR(amount_in_rmb, 4)
                         WHEN amount_in_rmb LIKE 'RM%' THEN SUBSTR(amount_in_rmb, 3)
                         ELSE amount_in_rmb
                    END

    If there are other prefixes, add more WHEN clauses. I don't think there's an easy way to skip over all letters at the beginning with SQL.