CODEWITHSUNDEEP
mysqldatabase
Gray_Hound
Gray_Hound

Reputation: 127

Replacing values in database

While importing my database I've realized that i made a mistake and imported some of the numbers in the format of 1k instead of 1000, and 1m instead of 1000000.

I did see this : 

UPDATE YourTable
    SET YourColumn = REPLACE(YourColumn, '%', '');

However that would create a problem seeing how with 1.25k instead of 1250 i would get 1.25000

What would be a possible way to fix this.

Upvotes: 1

Views: 67

Answers (1)

davek
davek

Reputation: 22915

Use CASE statements:

update yourtable
set yourcolumn = 
case when length(yourcolumn) <> 
          length(replace(yourcolumn, 'k', '')) 
then
   replace(yourcolumn, 'k', '') * 1000 
else when length(yourcolumn) <> 
          length(replace(yourcolumn, 'm', '')) 
then
   replace(yourcolumn, 'm', '') * 1000000
else
   yourcolumn 
end

EDIT: I'm assuming that yourcolumn is a character column and not numeric.

Upvotes: 1

Related Questions