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)))