By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table tbl_name(text_num varchar(10));
Insert Into tbl_name Values('4.5M'),
('70M'),
('10');
Records: 3 Duplicates: 0 Warnings: 0
SELECT CAST(text_num AS DECIMAL(10,2)) * (text_num like '%M') * 1000000 +
CAST(text_num AS DECIMAL(10,2)) * (text_num NOT like '%M') AS num
FROM tbl_name
| num |
|---|
| 4500000.00 |
| 70000000.00 |
| 10.00 |
Insert Into tbl_name Values('5K')
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 NOT like '%K' AND text_num NOT like '%M') AS num
FROM tbl_name
| num |
|---|
| 4500000.00 |
| 70000000.00 |
| 10.00 |
| 5000.00 |
-- You may also cast the result as unsigned to remove 0s after the decimal point
SELECT CAST(
CAST(text_num AS DECIMAL(10,2)) * (text_num like '%M') * 1000000 +
CAST(text_num AS DECIMAL(10,2)) * (text_num NOT like '%M')
AS UNSIGNED
) num
FROM tbl_name
| num |
|---|
| 4500000 |
| 70000000 |
| 10 |
| 5 |