Andy Green
asked on
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
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
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.
ASKER
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
Andy
Just do it in the front-end application/consumer.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There is a nice term called "technical debt".. imho you have to deal with it.
Why concatenating it first, when you later have to pick it apart? Sounds - well - pretty unusual.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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...
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
ASKER
Thanks Guys. All sorted now.
A
A