Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Sql migration for data type change

I have encountered occasion where I have to change database column ‘price’ ‘s data type from string to decimal. Problem is in the database there are some incorrect existing values like ‘120.45.32’ this shold corrected into ‘12045.32’. I have tried, CAST to decimal but it gives ‘120.00’. How can I resolve this in the migration file?

I have tried CAST(price as DECIMAL (10,2)). Migration works but all existing decimal values replace to ‘.00’ like 120.45 -> 120.00

Tags:

2

Answers


  1. 0

    Like Barmar said, you need to remove all first dots.

    CREATE TABLE Vale (text_1 varchar(10));
INSERT INTO Vale VALUES ('120.45.32')


    SELECT * FROM Vale
    text_1
    120.45.32 
    UPDATE Vale 
SET text_1 = regexp_replace(text_1,'[.]','',1,1)
WHERE    LENGTH(text_1)
            - LENGTH( REPLACE ( text_1, ".", '') )  = 2;
SELECT * FROM Vale

    Rows matched: 1  Changed: 1  Warnings: 0
    text_1
    12045.32

    fiddle

    Login or Signup to reply.
  2. 0

    first Clean up the incorrect data
    then Convert to a DECIMAL type

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search