Normalize pricing on andorid market data

gistfile1.sql

# Prepare: add column 'price_value' and 'price_normalized' to you table

# 1) Run this one first
UPDATE top_android_apps
SET    price_value = 0; 

# 2) Run this one second
UPDATE top_android_apps
SET    price_value = CASE
                       WHEN ( country IN ( 'GB', 'AU', 'CA', 'HK', 'RU' ) ) THEN
                       SUBSTRING(price, 4)
                       WHEN ( country IN( 'BR', 'IT' ) ) THEN REPLACE(
                       SUBSTRING(price, 3), ',', '.')
                       WHEN ( country IN ( 'US', 'JP', 'KR' ) ) THEN
                       REPLACE(SUBSTRING(price, 2), ',', '')
                       WHEN ( country IN ( 'DE', 'ES' ) ) THEN REPLACE(
                       LEFT(price, Char_length(price)
                                   - 1), ',', '.')
                       WHEN ( country IN ( 'SE' ) ) THEN REPLACE(
                       LEFT(price, Char_length(price) - 2),
                                                         ',', '.')
                       ELSE 0
                     END
WHERE  price REGEXP '[0-9]'; 

# 3) Run this one last
UPDATE top_android_apps
SET    price_normalized = CASE
                            WHEN ( country = 'GB' ) THEN price_value * 1.5917
                            WHEN ( country = 'AU' ) THEN price_value * 1.0143
                            WHEN ( country = 'CA' ) THEN price_value * 0.9777
                            WHEN ( country = 'HK' ) THEN price_value * 0.1286
                            WHEN ( country = 'RU' ) THEN price_value * 0.0326
                            WHEN ( country = 'BR' ) THEN price_value * 0.5624
                            WHEN ( country = 'IT' ) THEN price_value * 1.3538
                            WHEN ( country = 'US' ) THEN price_value
                            WHEN ( country = 'JP' ) THEN price_value * 0.0128
                            WHEN ( country = 'KR' ) THEN price_value * 0.0009
                            WHEN ( country = 'DE' ) THEN price_value * 1.3538
                            WHEN ( country = 'ES' ) THEN price_value * 1.3538
                            WHEN ( country = 'SE' ) THEN price_value * 0.1493
                            ELSE 0
                          END;