Reputation: 539
I have table with a column mrr varchar(64)
. I can't change the type of the column.
This simple query:
select mrr, cast(mrr as decimal(10,2)) ass f_mrr
FROM projects
where mrr is not null and creator_fname='Lisa'
returns unexpected results. Here is a partial list:
mrr cast(mrr as decimal(10,2))
663.90 663.90
40.00 40.00
675.00 675.00
0.00 0.00
100.00 100.00
2,950.00 2.00
3,779.90 3.00
-8,482.00 -8.00
What's up with the last 3 rows? I'm assuming it's the CAST
that's doing this.
Can someone explain what's going on?
Why does it only do this for some rows and not others?
How can I reliably cast the varchar(64)
to decimal
?
Upvotes: 1
Views: 69
Reputation: 4211
try this:
set @a = "295.00";
select if(right(@a,2)=00,CONCAT_WS('.',convert(@a,decimal(10,2))),@a)
Upvotes: 0
Reputation: 425448
What's going on is a comma is not part of a valid number. MySQL stops parsing for a number at the first non "number" character.
When MySQL converts string to numeric, it converts only leading "number" chars, which comprise of optional leading sign, digits, optionally decimal point and more digits.
Everything else is truncated/ignored. This includes by the way, the whole string if it doesn't start with a "number".
You can try the following query:
select mrr, cast(trim(replace(mrr, ',', '')) as decimal(10,2)) as f_mrr
from projects
where mrr is not null and creator_fname = 'Lisa'
Upvotes: 4