Solved

Using a variable to limit the decimal places

Posted on 2016-10-18
10
75 Views
Last Modified: 2016-10-18
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
0
Comment
Question by:Andy Green
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41847866
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
 
LVL 3

Author Comment

by:Andy Green
ID: 41847876
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
 
LVL 34

Expert Comment

by:ste5an
ID: 41847877
Just do it in the front-end application/consumer.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 3

Author Comment

by:Andy Green
ID: 41847887
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
 
LVL 52

Assisted Solution

by:Ryan Chong
Ryan Chong earned 100 total points
ID: 41847895
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
 
LVL 34

Expert Comment

by:ste5an
ID: 41847906
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
 
LVL 13

Accepted Solution

by:
Nakul Vachhrajani earned 400 total points
ID: 41847973
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
 
LVL 3

Author Comment

by:Andy Green
ID: 41847991
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41848189
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
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 41848285
Thanks Guys. All sorted now.

A
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question