Issue with casting String to decimal in MySQL

Question

I have a column in a data set that contains a currency indicator and the corresponding amount of that currency (i.e USD 35,000.05). I am trying to get the substring of the column containing the number and convert it to a decimal. However, whenever I convert it, it only returns the first two digits of the number I am trying to get.

For example, this is what happens:

In this sample, this is my query:

    SELECT 
       SUBSTRING(`Annualized Opportunity Amount`,1,3) as `test1`,
       (SUBSTRING(`Annualized Opportunity Amount`,5)) as test2,
       case when SUBSTRING(`Annualized Opportunity Amount`,1,3) = 'AUD' 
              then CAST(SUBSTRING(`Annualized Opportunity Amount`,5) AS DECIMAL(10,2)) 
       else 0 END as `test3`
    from annualized_opportunity_revenue

Is there any reason as to why only the first 2 digits of the test2 column would be returned in test3? The substrings for the resulting value are the same so I don't understand why casting it would alter the data.

Any help will be appreciated, thank you.

You need to replace , first, 12,345 isn't a number. Store number as number, then there will be no problem. — pavel
– pavel, Commented Jan 30, 2018 at 16:28
Try select cast(replace(dacolumn, ',' , '') as decimal(10,2)) — clinomaniac
– clinomaniac, Commented Jan 30, 2018 at 16:31

Shadow · Accepted Answer · 2018-01-30 16:31:20Z

1

You must remove the comma from the string before casting it to a number:

CAST(SUBSTRING(REPLACE(`Annualized Opportunity Amount`,',',''),5) AS DECIMAL(10,2))

answered Jan 30, 2018 at 16:31

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issue with casting String to decimal in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related