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

Question

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 ?

Tim Biegeleisen · Accepted Answer · 2021-08-16 04:53:56Z

1

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

answered Aug 16, 2021 at 4:53

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason Xu Over a year ago

thanks, @Tim. hope it's supported in mysql cos it's similar to datetime handling. but if not we definitely can use this approach.

Collectives™ on Stack Overflow

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

1 Answer 1

Demo

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related