Converting / Casting an nVarChar with Comma Separator to Decimal

Question

I am supporting an ETL process that transforms flat-file inputs into a SqlServer database table. The code is almost 100% T-SQL and runs inside the DB. I do not own the code and cannot change the workflow. I can only help configure the "translation" SQL that takes the file data and converts it to table data (more on this later).

Now that the disclaimers are out of the way...

One of our file providers recently changed how they represent a monetary amount from '12345.67' to '12,345.67'. Our SQL that transforms the value looks like SELECT FLOOR( CAST([inputValue] AS DECIMAL(24,10))) and no longer works. I.e., the comma breaks the cast.

Given that I have to store the final value as Decimal (24,10) datatype (yes, I realize the FLOOR wipes out all post-decimal-point precision - the designer was not in sync with the customer), what can I do to cast this string efficiently?'

Thank you for your ideas.

Answer 1

try using REPLACE (Transact-SQL):

SELECT REPLACE('12,345.67',',','')

OUTPUT:

12345.67

so it would be:

SELECT FLOOR( CAST(REPLACE([input value],',','') AS DECIMAL(24,10)))

Answer 2

This works for me:

DECLARE @foo NVARCHAR(100)
SET @foo='12,345.67'

SELECT FLOOR(CAST(REPLACE(@foo,',','') AS DECIMAL(24,10)))

This is probably only valid for collations/culture where the comma is not the decimal separator (ie: Spanish)

Answer 3

SELECT FLOOR (CAST(REPLACE([inputValue], ',', '') AS DECIMAL(24,10)))

Answer 4

While not necessarily the best approach for my situation, I wanted to leave a potential solution for future use that we uncovered while researching this problem.

It appears that the SqlServer datatype MONEY can be used as a direct cast for strings with a comma separating the non-decimal portion. So, where SELECT CAST('12,345.56' AS DECIMAL(24,10)) fails, SELECT CAST('12,345.56' AS MONEY) will succeed.

One caveat is that the MONEY datatype has a precision of 4 decimal places and would require an explicit cast to get it to DECIMAL, should you need it.