# Return value formatted similar to if casted as money

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
-

## migrated from stackoverflow.comFeb 11 '14 at 21:38

This question came from our site for professional and enthusiast programmers.

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

-
TIL: REPLICATE accepts negative lengths (returning NULL as a result). – Andriy M Feb 12 '14 at 6:24