I have a column in a table called [AMOUNT]. The column contains value uploaded from an excel sheet in the Currency Format (i.e $158.96, ($78.36), $714.98, ($7.73), etc). How do I convert these values to a decimal so that it no longer has the $ sign or the parentheses (but it should still have a negative sign for the parentheses values)?

What I thought would work is below and it worked on a similar problem but the values were slightly different (it didn't have parentheses for the negative values).

CAST(CONVERT(VARCHAR(10), CONVERT(MONEY, [AMOUNT])) AS DECIMAL(14,4) AS AMOUNT_V2

CodePudding user response：

You need to deal with the parenthesis to result in a negative number - one way would be to use translate, such as

select Cast(Translate('($78.36)','()','- ') as money)

Edit - use nested replace if 2016 or prior

select Cast(Replace(Replace('($158.45)','(','-'),')','') as money)