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

CASTING a number in a TEXT datatype to DOUBLE returns 0 in MySQL

I have a table with a coulmn of number which is saved in TEXT datatype.
When i want to CAST it to DOUBLE datatype it returns 0.
Here is my data

product_price
$9.99
$89.85
$89.85
$89.85
$89.85
$799.99
$58.89
$45.99
$399.95
$299.99
$234.50
$199.95
$169.95
$169.95
$169.95
$13.99
$13.99
$13.99
$13.99
$13.99
$1,000.00
$1,000.00
$1,000.00
$1,000.00
$1,000.00
$1,000.00
$1,000.00
$1,000.00
$1,000.00

my code is here:

SELECT CAST(product_price AS DOUBLE)
FROM test_db.transactions
ORDER BY product_price DESC;

and the return vales are as follows:

CAST(product_price AS DOUBLE)
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

I will be grateful if someone can help me handle this problem.

Tags:

5

Answers


  1. 0

    MySQL casting to double is added in MySQL 8.0.17 . See Cast Functions and Operators .

    For non supporting version you can use a workaround , still the $ sign and , needs to be replaced.

    Try,

    select product_price,
       replace(replace(product_price,'$',''),',','') + 0.0 as new_product_price 
from transactions;

    For MySQL versions supporting casting to double use,

    select product_price,
       cast(replace(replace(product_price,'$',''),',','') as double)  as new_product_price 
from transactions;

    See examples here

    My suggestion, use decimal to store money values.

    See Best data type to store money values in MySQL

    Login or Signup to reply.
  2. 0

    The $ sign can’t be converted to double. First, you have to remove the $ sign then double the value

    SELECT replace(product_price, '$', '') + 0.0 as product_price FROM products order by product_price desc;
    Login or Signup to reply.
  3. 0

    MySQL supports casting into DECIMAL.
    You can see more here
    But the problem you have is mentioned in the above answers,
    half the solution can be taken from there:

    You can use the following:

    SELECT 
CAST(REPLACE(c.`product_price`,'$','') AS DECIMAL) as product_price 
FROM products c;

    Here is a link to fiddle

    Login or Signup to reply.
  4. 0

    Both $ and , signs need to be removed from the value before casting, so that $1,000.00 becomes 1000.00 first:

    SELECT CAST(
  REPLACE(REPLACE(product_price, ",", ""), "$", "")
  AS DOUBLE
)
FROM test_db.transactions
ORDER BY product_price DESC;

    Side note:

    As mentioned above by @ergest, better use DECIMAL(20,4) type for amounts, together with additional currency CHAR(3) field specifying, well, currency of the amount. Or, if prices in this table are by design in USD (even if sold on other markets), then the field should be product_price_usd. The world is big, don’t limit yourself 😉

    Login or Signup to reply.
  5. 0

    Strings that start with a non-numeric character are treated as 0 when type conversion is attempted.

    The following examples illustrate conversion of strings to numbers for comparison operations:

    mysql> SELECT 1 > ‘6x’;
    -> 0
    mysql> SELECT 7 > ‘6x’;
    -> 1
    mysql> SELECT 0 > ‘x6’;
    -> 0
    mysql> SELECT 0 = ‘x6’;
    -> 1

    (https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html)

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

Back To Top
Search