Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Feb 11 at 21:38

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

1 Answer 1

up vote 5 down vote accepted

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

share|improve this answer
    
TIL: REPLICATE accepts negative lengths (returning NULL as a result). –  Andriy M Feb 12 at 6:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.