Converting / casting nVarChar comma-delimited to decimal

Question

I support an ETL process that converts the input of a flat file to a SqlServer database table. The code is almost 100% T-SQL and works inside the database. I do not own the code and cannot change the workflow. I can only help set up an SQL "translation" that takes file data and converts it to table data (more on that later).

Now that the disclaimer ...

One of our file providers has recently changed how they represent a monetary amount from '12345.67' to '12,345.67' . Our SQL, which converts the value, looks like SELECT FLOOR( CAST([inputValue] AS DECIMAL(24,10))) and does not work anymore. I., comma breaks cast.

Given that I need to save the final value as a Decimal (24,10) datatype (yes, I understand that FLOOR erases all precision after the decimal point - the designer did not synchronize with the client), what can I do to use this line effectively? ''

Thanks for your ideas.

Answer 1

try using REPLACE (Transact-SQL) :

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

OUTPUT:

 12345.67 

so it will be:

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

Answer 2

Although this was not always the best approach to my situation, I wanted to leave a potential solution for future use that we discovered when exploring this problem.

It seems that the SqlServer MONEY data type can be used as a live broadcast for semicolon strings separating the non-decimal part. So where SELECT CAST('12,345.56' AS DECIMAL(24,10)) does not work, SELECT CAST('12,345.56' AS MONEY) will succeed.

One caveat is that the MONEY data type has an accuracy of 4 decimal places and requires an explicit cast to get it to DECIMAL if you need it.

Answer 3

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 true for comparisons / culture where the comma is not a decimal separator (e.g. Spanish)

Answer 4

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