How to convert formatted string like "120k" to integer/double in Mysql

user_1682262 0
Viewed 25

Sometimes we got raw data of number is imported in a human readable string format, e.g. "955.37K". In Mysql, is there any built-in method to convert it back to computable types, e.g. 955370.0 ?

mysql
1 Answers

You could use a CASE expression with the help of regex substring logic:

SELECT
    data,
    CASE REGEXP_SUBSTR(data, '[A-Z]$')
         WHEN 'K' THEN 1000
         WHEN 'M' THEN 1000000
         WHEN 'B' THEN 1000000000
         ELSE 1 END *
    CAST(REGEXP_SUBSTR(data, '^\\d+(\\.\\d+)?') AS UNSIGNED) AS full_data
FROM yourTable;

Demo

user_18632290
Related