SSIS – Return Value Formatted as Money in SQL Server 2008 R2

sql-server-2008-r2ssis

My client's system is very outdated, and there is nothing I can do about it. What I'm trying to achieve is to extract values always with at least 2 decimal places.

E.g.

From 

1826.5695600000  
1826.3000000000  
34.2300000000  
0.0030000000

To

1826.56956 
1826.30 
34.23 
0.003

If I cast these values as money I lose everything after 4th decimal place, but I need to achieve something similar but with min 2 max 10 decimal places. It does not matter if it is a string at the end, all that matters that I can extract value in this format.

My first try was to cast to float, find decimal point and accordingly cast the value as decimal(20,variable) but that was impossible, so I am now stuck in my mind how to achieve this.

These values are going trough SSIS processes

  • Extracted from Database
  • Staged
  • Pushed to client's import file

Best Answer

This is really ugly and hopefully serves as a lesson as to why you should be fixing the receiving system to not be so fussy about trailing zeroes.

DECLARE @t TABLE(a DECIMAL(20,10));

INSERT @t VALUES
(1826.5695600000),  
(1826.3000000000),  
(  34.2300000000),  
(   0.0030000000);

DECLARE @d INT = 10; -- number of decimal places in source data!

;WITH s(a,dot,nz) AS 
(
  SELECT 
    CONVERT(VARCHAR(32),a),
    PATINDEX('%.%',CONVERT(VARCHAR(32),a)),
    PATINDEX('%[^0]%',REVERSE(a))
  FROM @t
)
SELECT LEFT(a,dot)+SUBSTRING(a,dot+1,@d+1-nz)
  + COALESCE(REPLICATE('0',nz-(@d-1)),'')
FROM s;

Results:

1826.56956
1826.30
34.23
0.003

This assumes the source data type is a decimal(something,10). If it is a string, you need to have some extra precautions in here for values that aren't numbers (or aren't really numbers in this sense).

Related Question