CODEWITHSUNDEEP
mysqlstringcastingdecimal
boing
boing

Reputation: 539

Odd behavior when trying to cast varchar to decimal

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

Answers (2)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

try this:

set @a = "295.00";
select if(right(@a,2)=00,CONCAT_WS('.',convert(@a,decimal(10,2))),@a)

Upvotes: 0

Bohemian
Bohemian

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

Related Questions