Question 167270 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Convert currency in SQL

anybody know how can I convert the inconsistent value column (contain both number and text) into number?
like this 4.5M => 4500000, 7M => 7000000

I tried convert but it doesn’t give me an expected answer. Thank you so much

Tags:

2

Answers


  1. 0

    You can CAST the column into decimal then multiply the number by 1000 if K, 1000000 if M, and so on :

    SELECT *, CAST(col AS DECIMAL(10,2)) * IF(col LIKE '%K', 1000, 1000000) as number
FROM mytable
WHERE col LIKE '%[KM]'
    Login or Signup to reply.
  2. 0

    In MySQL, logical operators evaluate to 1 (for TRUE) and 0 (for FALSE), we can use this as the following:

    SELECT CAST(text_num AS DECIMAL(10,2)) * (text_num like '%M') * 1000000 +
       CAST(text_num AS DECIMAL(10,2)) * !(text_num like '%M') AS num
FROM tbl_name

    If you have units other than M, you can expand the query as the following:

    SELECT CAST(text_num AS DECIMAL(10,2)) * (text_num like '%K') * 1000 + 
       CAST(text_num AS DECIMAL(10,2)) * (text_num like '%M') * 1000000 +
       CAST(text_num AS DECIMAL(10,2)) * !(text_num like '%K' or text_num like '%M') AS num
FROM tbl_name

    demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search