Is there a simple built-in way of converting NVARCHARs in the format "$1,000.00" and "($1,000.00)" to the numerical values 1000.00 and -1000.00 respectively?
I'm trying to do this in either SQL Server or SSIS.
Casting to MONEY gives me the error
"Cannot convert a char value to money. The char value has incorrect syntax.".
when attempting to cast the negative value, I'm assuming due to the parenthesis.
In SQL Server, you can use the T-SQL FORMAT() function to format a number as a currency. The FORMAT() function allows you to format numbers, dates, currencies, etc. It accepts three arguments; the number, the format, and an optional “culture” argument.
SQL Server's CAST() and CONVERT() methods can be used to convert VARCHAR to INT. We'll also look at the more efficient and secure approach to transform values from one data type to another.
If you are planning to convert varchar to float you should know that these two data types are not compatible with each other. In the earlier versions of SQL Server you had to use CASE, ISNUMERIC & CONVERT to convert varchar to float but in SQL Server 2012, you can do it with just one function TRY_CONVERT.
This should do the trick
SELECT '$1,000.00'
,CAST('$1,000.00' AS MONEY)
,CAST(REPLACE(REPLACE('($1,000.00)', '(', '-'), ')','') AS MONEY)
SQL Fiddle Example
And per @mellamokb's suggestion, if you're using SQL 2012, you can use this:
SELECT PARSE('($1000.00)' AS MONEY)
SQL Fiddle Example
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With