Mysql- replace third to last character with RIGHT function without losing the rest of value-CodePudding

I have columns with numeric values like this:

14.333,67
3.123,90
1.234.222,01

and so on. i've written a small statement that will convert the periods to commas via the REPLACE function.

UPDATE table SET column = REPLACE (column, '.', ',')

now what i need to do is update that last comma value to be a period. the datatype is char and i need to keep it that way.

I'm trying to use the RIGHT function like this.

REPLACE (RIGHT(column, 3), ',', '.')

which only seems to pull the last three values. For example after I run these two statements I get the following results in order:

original: 14.333,67
first update: 14,333,67
last update: .67

how can i squeeze all of this into one update/set statement to get the full value?

CodePudding user response：

Try to chain few REPLACEs. You will need three in one row due to you have to temporarily replace one of your replaced characters to something which doesn't match to the second replaced character.

    SELECT
        REPLACE(
            REPLACE(                            
                REPLACE(IFNULL('123.456,78', ''), 
                    ',', ';'), 
                    '.', ','), 
                    ';', '.') as result;

result
123,456.78

So, your update query will be like:

UPDATE
    table
SET
    column = REPLACE(
                REPLACE(                            
                    REPLACE(IFNULL(column, ''), 
                        ',', ';'), 
                        '.', ','), 
                        ';', '.')

CodePudding user response：

You can concatenate the rest of the string with the part you're replacing in.

SET column = CONCAT(LEFT(column, LENGTH(column)-3), REPLACE(RIGHT(column, 3), ',', '.'))