CODEWITHSUNDEEP
sqlsql-servert-sqlcastingssis
Cavyn VonDeylen
Cavyn VonDeylen

Reputation: 4249

Casting String to Money Value in SQL Server

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.

Upvotes: 9

Views: 43087

Answers (1)

Tom Halladay
Tom Halladay

Reputation: 5761

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

Upvotes: 18

Related Questions