Solved

Using a variable to limit the decimal places

Posted on 2016-10-18
10
62 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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 49

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 33

Expert Comment

by:ste5an
ID: 41847877
Just do it in the front-end application/consumer.
0
 
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 49

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 33

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 12

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 48
SQL Server Question 5 28
SQL - SP needs a little help 9 22
recover sqlserver db 8 56
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now