Is there a simple built-in way of converting NVARCHARs in the format "
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?
是否有一种简单的内置方式将格式为“$ 1,000.00”和“($ 1,000.00)”的NVARCHAR分别转换为数值1000.00和-1000.00?
I'm trying to do this in either SQL Server or SSIS.
我试图在SQL Server或SSIS中执行此操作。
Casting to MONEY gives me the error
施放到MONEY会给我错误
"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.
当试图抛出负值时,我假设由于括号。
1 个解决方案
#1
10
This should do the trick
这应该可以解决问题
SELECT '$1,000.00'
,CAST('$1,000.00' AS MONEY)
,CAST(REPLACE(REPLACE('($1,000.00)', '(', '-'), ')','') AS MONEY)
SQL小提琴示例
And per @mellamokb's suggestion, if you're using SQL 2012, you can use this:
根据@ mellamokb的建议,如果你使用的是SQL 2012,你可以使用:
SELECT PARSE('($1000.00)' AS MONEY)
SQL小提琴示例
,000.00" and "(
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?
是否有一种简单的内置方式将格式为“$ 1,000.00”和“($ 1,000.00)”的NVARCHAR分别转换为数值1000.00和-1000.00?
I'm trying to do this in either SQL Server or SSIS.
我试图在SQL Server或SSIS中执行此操作。
Casting to MONEY gives me the error
施放到MONEY会给我错误
"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.
当试图抛出负值时,我假设由于括号。
1 个解决方案
#1
10
This should do the trick
这应该可以解决问题
SELECT '$1,000.00'
,CAST('$1,000.00' AS MONEY)
,CAST(REPLACE(REPLACE('($1,000.00)', '(', '-'), ')','') AS MONEY)
SQL小提琴示例
And per @mellamokb's suggestion, if you're using SQL 2012, you can use this:
根据@ mellamokb的建议,如果你使用的是SQL 2012,你可以使用:
SELECT PARSE('($1000.00)' AS MONEY)
SQL小提琴示例
,000.00)" to the numerical values 1000.00 and -1000.00 respectively?Is there a simple built-in way of converting NV
