February 16, 2018 at 10:14 am
below86 - Friday, February 16, 2018 9:20 AMSELECT original_value,
CAST(ROUND(CONVERT(INT, original_value) / 10.0, 0) AS DECIMAL(9, 3)) AS decimalvalue
FROM (VALUES('0054880'), ('0054885'), ('02120'), ('00002124'), ('2125')) x(original_value)
;
Results:original_value decimalvalue
0054880 5488.000
0054885 5489.000
02120 212.000
00002124 212.000
2125 213.000
My question would be why do you need the 3 trailing zeroes if you are rounding up?
I don't... These aren't my requirements... Ask the OP.
February 16, 2018 at 10:33 am
Jason A. Long - Friday, February 16, 2018 10:14 AMbelow86 - Friday, February 16, 2018 9:20 AMSELECT original_value,
CAST(ROUND(CONVERT(INT, original_value) / 10.0, 0) AS DECIMAL(9, 3)) AS decimalvalue
FROM (VALUES('0054880'), ('0054885'), ('02120'), ('00002124'), ('2125')) x(original_value)
;
Results:original_value decimalvalue
0054880 5488.000
0054885 5489.000
02120 212.000
00002124 212.000
2125 213.000
My question would be why do you need the 3 trailing zeroes if you are rounding up?I don't... These aren't my requirements... Ask the OP.
My questions was directed at the OP. I posted some code to show the OP that it shouldn't matter how many leading zeroes(or none) that you have that this will solve the issue. The issue I have with your code Jason is that it assumes the input data will always be the same length. You never want to assume, so I avoided the 'STUFF' statement.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 16, 2018 at 11:06 am
below86 - Friday, February 16, 2018 10:33 AMJason A. Long - Friday, February 16, 2018 10:14 AMI don't... These aren't my requirements... Ask the OP.My questions was directed at the OP. I posted some code to show the OP that it shouldn't matter how many leading zeroes(or none) that you have that this will solve the issue. The issue I have with your code Jason is that it assumes the input data will always be the same length. You never want to assume, so I avoided the 'STUFF' statement.
Sorry... Not sure why but the 1st time I looked at it it it looked like you'd quoted my previous post. (I need to quit eating paint chips apparently) Sorry about that...
As far as the 7 characters & the stuff function... All of the OPs test values are 7 padded characters... It's only logical to assume that all values conform to that convention. STUFF is one of the more useful functions available, avoiding it unnecessarily is like avoiding the use of your thumbs... Sure you can do it but there's no good reason to do so.
February 16, 2018 at 11:38 am
IQ1 - Thursday, February 15, 2018 1:41 PMHi there,
This works perfectly except for cases where there are leading zeros as is 0002125, I would like the output to be 213.000 instead its removing the leading zeros and giving me a different output, can this be fixed in the Query above. Thanks again
In this post the OP only has 7 positions, and I think it was stated somewhere about an issue with more leading zeroes. I was only avoiding the STUFF because of the uncertainty of the data, not because I didn't think it is useful.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 16, 2018 at 12:45 pm
below86 - Friday, February 16, 2018 11:38 AMIQ1 - Thursday, February 15, 2018 1:41 PMHi there,
This works perfectly except for cases where there are leading zeros as is 0002125, I would like the output to be 213.000 instead its removing the leading zeros and giving me a different output, can this be fixed in the Query above. Thanks againIn this post the OP only has 7 positions, and I think it was stated somewhere about an issue with more leading zeroes. I was only avoiding the STUFF because of the uncertainty of the data, not because I didn't think it is useful.
Even in the "more leading zeros" examples, they were limited to 7 characters. That said, as long as the more flexible solution doesn't add unnecessary costs when compared to the more rigid version, I can't/won't argue against it.
February 16, 2018 at 1:16 pm
If I was going to use the STUFF I would want to make sure the field was always the same length. And since I may not always be able to count on the source to provide me the data in the correct format I would have to code the STUFF statement like line 2 of this SQL.SELECT original_value,
ROUND(CONVERT(DECIMAL(9,3), STUFF(RIGHT(CONCAT('0000000', RTRIM(LTRIM(original_value))), 7), 7, 0, '.')), 0) AS Stuffed,
CAST(ROUND(CONVERT(INT, original_value) / 10.0, 0) AS DECIMAL(9, 3)) AS decimalvalue
FROM (VALUES('0054880'), ('0054885'), ('02120'), ('00002124'), ('2125')) x(original_value)
;
My version seems a little cleaner. IMHO.🙂
And a note to anyone looking at this code and is wondering why the divide is with 10.0 and not just 10, an INT divided by an INT will NOT return any decimal places.(Example 2125/10 would return 212 not 212.5) I'm sure a lot of people know that but it's something that bit me a few years back. I could have avoided using the 10.0 by converting the original_value to a DECIMAL(9,3) instead on to an INT. But converting it to a decimal first then divide by 10 returns more than 3 positions after the decimal, so another convert would be needed.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply