Using a variable to limit the decimal places

I have a numeric data field (Numeric(18,6)) This is concatenated into a string with some other text values.

Trouble is not all clients want 6 decimal places after the int. I have tried Round() but his retains the 6 decimal places. Converting to a varchar does the job, but how to I make it a variable from probably 2 to 6 in length.

I have a setting table where this value is stored.

Andy
LVL 3
Andy GreenAsked:
Who is Participating?
 
Nakul VachhrajaniConnect With a Mentor Technical Architect, Capgemini IndiaCommented:
Round does, and should work (see example below). I guess, you may not be passing the precision parameter to the ROUND() function which is why it may not be working in your case.

Of course, in order to concatenate it, you will need to cast it back to VARCHAR/NVARCHAR.

USE tempdb;
GO
DECLARE @valueOfPi DECIMAL(18,6);
DECLARE @requiredDecimals INT = 2;

SELECT @valueOfPi = PI();

SELECT @valueOfPi AS ActualValue, 
       ROUND(@valueOfPi, @requiredDecimals) AS RoundedValue, 
       CAST(ROUND(@valueOfPi, @requiredDecimals) AS VARCHAR(20)) AS StringRepresentation;

/* RESULTS
ActualValue  RoundedValue  StringRepresentation
------------ ------------- --------------------
3.141593     3.140000      3.140000
*/

Open in new window

0
 
Ryan ChongCommented:
to make your system more robust, just try to store the values in 6 decimal points, and when you want to output it, read the setting table and format the value accordingly.
0
 
Andy GreenAuthor Commented:
Hi Ryan - this is what I'm doing the number is stored to 6dp, and I'm asking how to display a lower dp based on my setting table.

Andy
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
ste5anSenior DeveloperCommented:
Just do it in the front-end application/consumer.
0
 
Andy GreenAuthor Commented:
Hi ste5an - not able to do in front end easily.

This is someone else's code and a bit convoluted. The string is concatenated in SQL, and then parsed through an XML interpreter for the final display. Would take longer than I have to pick apart the XML and apply a transform.

For now I can change the stored proc to use Varchar(4) but not ideal.

Andy
0
 
Ryan ChongConnect With a Mentor Commented:
this is what I'm doing the number is stored to 6dp, and I'm asking how to display a lower dp based on my setting table
you can always do that in front-end as what already mentioned by ste5an, or you can create a stored procedure, in which it reads the setting table and then customize the values if necessary.

try customize:
CREATE PROCEDURE  [dbo].[getRecords]
AS
BEGIN
	 SET NOCOUNT ON;  	
	
	 DECLARE @SQL varchar(500
	 DECLARE @DecimalPts VARCHAR(20)
	 
	 SET @DecimalPts = (SELECT Value FROM Setting WHERE SettingField = 'DecimalPoints')
	 
     SET @SQL = 'SELECT *, COnvert(decimal(18, '+@DecimalPts+'), MyField1) FROM Table1'
	
	 EXEC(@SQL);
END

Open in new window

0
 
ste5anSenior DeveloperCommented:
There is a nice term called "technical debt".. imho you have to deal with it.

This is someone else's code and a bit convoluted. The string is concatenated in SQL, and then parsed through an XML interpreter for the final display. Would take longer than I have to pick apart the XML and apply a transform.

Why concatenating it first, when you later have to pick it apart? Sounds - well - pretty unusual.
0
 
Andy GreenAuthor Commented:
Thanks Nakul - but as you can see from your example Round() still displays 6 decimals padded with zeros, I'd be looking for a result of 3.14.

ste5an - the SQL is quite long so would take some working out to use your approach, but does seem to be the only way.

I know technical debt, and we have a ton of it, from varchars with no specified lengths, unnecessary cursors, even no Using statements in the front end classes to auto clean up.

Its a bit of a mess, with urgent changes required and no idea what the code is doing - ho hum...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You'll need to truncate the converted value so it will get rid of the 0's. Check the LEFT function below:
DECLARE @MyValue Numeric(18,6)=RAND()
DECLARE @MyString VARCHAR(100)='The number is:'
DECLARE @DecPlaces INT = 5

DECLARE @MyCastValue VARCHAR(20) = CAST(ROUND(@MyValue, @DecPlaces) AS VARCHAR)

SET @MyCastValue = LEFT(@MyCastValue, CHARINDEX('.', @MyCastValue)+@DecPlaces)
SET @MyString = @MyString + @MyCastValue

PRINT @MyCastValue
PRINT @MyValue
PRINT @MyString

Open in new window

0
 
Andy GreenAuthor Commented:
Thanks Guys. All sorted now.

A
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.