Convert a Negative Number with Parentheses to a Minus in MYSQL

I开发者_StackOverflow社区 have a MYSQL database with Negative numbers that are enclosed in parenthesis

eg. (14,500) which is supposed to be -14500.

I am storing the numbers as varchar. I am trying to convert all the numbers to a double or float format and also format the negative numbers with a minus sign.

My code:

select case 
   when substr(gross_d,1,1) = '(' then
       ltrim('(') and rtrim(')') *-1
   else 
      (gross_d)
   end gross_d_num
from buy;
convert(gross_d_num,Double);

The problem with my current method is all the negative numbers with the parenthesis are converted to zero. Is there a different method to get my result.

edit:

I also removed the *-1 to see if the Parenthesis is removed and I get a value of zero.

Something like

convert (
    case 
       when gross_d LIKE '(%)' THEN CONCAT('-', REPLACE(REPLACE(gross_d, ')', ''), '(', ''))
       else gross_d
    end, decimal(19,6))

Here, you are trimming parenthesis only. This becomes zero when you multiply by -1

ltrim('(') and rtrim(')') *-1

CONVERT( 
    IF( gross_d LIKE '(%)' 
        ,CONCAT( '-', SUBSTR( gross_d, 1, LENGTH( gross_d ) - 2 ) )
        ,gross_d )
,DECIMAL );

At our company we don't have control over currency formatting used by external parties uploading excel sheets. We currently use this to convert the currencies and add a case whenever something new shows up :

SET @netSale := '$ (154.00)';

SELECT CONVERT (

CASE 
       when @netSale LIKE '$ (%)' THEN CONCAT('-', REPLACE(REPLACE(REPLACE(@netSale, '$ ', ''), ')', ''), '(', ''))
       when @netSale LIKE '(%)' THEN CONCAT('-', REPLACE(REPLACE(REPLACE(@netSale, '$ ', ''), ')', ''), '(', ''))
       else REPLACE(REPLACE(@netSale,'$',''),',', '')
    END, DECIMAL(10,2)
)

This deals with most formatting styles we have encountered and is especially useful when loading a converted CSV file to a table.