October 18, 2012 at 3:59 am
L' Eomot Inversé (10/18/2012)
Good questionBut the explanation is very poor, inaccurate. For example the length of the expression "123.45" is 6, but str(123.45,4,2) is not '**' as suggested by the explanation but '123'.
STR (123.45,3,2) will also give the same result i.e. "123" but it doesn't look like the fault of author as it seems MS itself is not clear about the explanation (author has provided the exact statement from msdn article in his explanation).
However, this part of msdn article makes it clear (see the Remarks section):
From BOL:
If supplied, the values for length and decimal parameters to STR should be positive. The number is rounded to an integer by default or if the decimal parameter is 0. The specified length should be greater than or equal to the part of the number before the decimal point plus the number's sign (if any).
BOL URL : http://msdn.microsoft.com/en-us/library/ms189527.aspx
October 18, 2012 at 4:15 am
(It's a good thing I remember to copy my reply to the clipboard before posting it - I ran once more in the issue [probably timeout related, I always get it after spending a lot of time on a reply] where I get an error and lose my reply, but now I could just refresh the site and paste my answer!)
L' Eomot Inversé (10/18/2012)
Good questionBut the explanation is very poor, inaccurate. For example the length of the expression "123.45" is 6, but str(123.45,4,2) is not '**' as suggested by the explanation but '123'.
And unfortunately, the explanation in Books Online is only marginally better. :angry: That goes for both the SQL Server 2008 version (linked in the question's explanation) and the SQL Server 2012 version (http://msdn.microsoft.com/en-us/library/ms189527.aspx). They are even partially incorrect!!!
Based on some tests I ran, this is how STR apparently works:
* The first argument (value) is mandatory. It can be any data type, but a conversion error will be raised if it can't be converted to float (!!).
* The second argument (length) is optional. If omitted, 10 will be assumed. If length is not an integer, an error will be raised. If the length is less than 1 or more than 8000, NULL will be returned. Otherwise, a varchar string of the specified length will be returned.
* The third argument (decimal) is optional. If omitted, 10 will be assumed. It is not possible to specify decimal without specifying length, but the reverse is possible. If decimal is not an integer, an error is raised. If decimal is less then 0, NULL will be returned. Otherwise, the requested precision will be determined as the minimum of decimal and 16.
1. The length of the integer fraction is determined. This is equal to the number of digits in value before the decimal seperator, plus one if value is negative. If the length of the integer fraction is less then length, the returned value will be a string of length '*' characters.
2. The maximum available precision is determined. This is equal to length - (the length of the integer fraction + 1). If that calculation returns -1, the maximum available precision is set to 0.
3. The actual precision is now determined as the lowest of the requested precision and the maximum available precision.
4. If the actual precision is 16, value is truncated to that number of decimal places1. If the actual precision is between 0 and 15, vallue is rounded to that number of decimal places.
5. The rounded or truncated value is now converted to string. When needed, trailing zeroes are added to get the number of positions after the deimal seperator equal to the actual precision. The formatted value is then right-adjusted - i.e., leading spaces are added to ensure that the returned string uses all available positions2.
Notes:
1 This is documented incorrectly in Books Online. The documentation suggests that truncation will be used if the specified decimal is more than 16, but truncation is actually also used when it is equal to 16.
2 Since the value returned will always use the maximum available length of the returned string, it would probably make more sense to return a fixed-length char(length) value. However, I doublechecked, and the value returned is actually varchar(length).
October 18, 2012 at 4:18 am
Good question. I had to reference BOL to get the answer. Learned about STR. Thanks.
October 18, 2012 at 4:24 am
Very well explained Hugo 🙂
October 18, 2012 at 4:24 am
L' Eomot Inversé (10/18/2012)
@Cassie (10/18/2012)
L' Eomot Inversé (10/18/2012)
Good questionBut the explanation is very poor, inaccurate. For example the length of the expression "123.45" is 6, but str(123.45,4,2) is not '**' as suggested by the explanation but '123'.
It is an exact copy from the http://msdn.microsoft.com/en-us/library/ms189527(v=sql.100).aspx, first example!
The examples on that page contain no explanations, so how can the explanation be an exact copy of something in one of them?
Whether one considers it an explanation or not, fact is that the text that shows up, marked as "Explanation", on this site after answering the question, is a verbatim copy of a sentence in Books Online. I had not even noticed that when I first posted to this topic (mentioning the striking similarity of the question and the example).
And while we're speaking of that...
Stuart Davies (10/18/2012)
But if the question serves to educate, test or whatever - I don't see the harm
The harm is that this website is now in danger of being the target of legal action. When I though it was just the example (and not the "explanation" as well), I was undecided on whether this is plagiarism or fair use. Now, I have made up my mind on how to categorize this.
Frankly, I don't think that any serious harm for this website will come of this. But I do think that it is very careless of the submitter of this question to not consider the possible legal ramifications for SQLServerCentral.com from simply lifting content from a copyrighted source and submitting it. When submitting a question, you have to tick a box that says: "I own the copyright for this material, or have permission from the owner" - -so I don't think the question author can claim ignorance.
October 18, 2012 at 4:37 am
Thanks for attractive question.;-)
October 18, 2012 at 6:40 am
Do I get to keep the point if the question gets removed and the plagiarist is thrown into timeout? 😛
Aigle de Guerre!
October 18, 2012 at 7:35 am
(From Hugo):The harm is that this website is now in danger of being the target of legal action.
Well, maybe. Granted, we here in the States may like to hit the courtroom a bit more than we should, but I would bet that a good case could be made that posting the link where the quote came from could be used to state that it was the reference where the explanation came from.
Is it bad form to post as a question an exact string from MSDN? Well, we could probably debate that all day. Will Microsoft bring down upon SQLServerCentral its battery of lawyers for this one post? Probably not, since this site is not making any money off the QOTD. I think the worst that could happen is that Steve would be asked to pull the question, and I doubt that will happen either.
October 18, 2012 at 8:01 am
Hugo Kornelis (10/18/2012)
I knew about the STR function, but was not entirely sure what the optional arguments meant, so I decided to check Books Online before answering.
I ran into the same issue Hugo... I rarely use this function, and then it's without the last two parameters. I also needed that BOL refresh
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 18, 2012 at 8:05 am
The example was taken directly from Books Online (August 2008)
When the expression exceeds the specified length, the string returns ** for the specified length.
Copy Code
SELECT STR(123.45, 2, 2);
GO
Here is the result set.
Copy Code
--
**
(1 row(s) affected)
October 18, 2012 at 8:08 am
What is the use case for the STR function? Namely, when would this be used as opposed to just doing a convert? A round can be used to keep the number of decimal places down so I don't think that STR has an advantage there, particularly since you may not get the number returned as the question shows.
October 18, 2012 at 8:10 am
The harm is that this website is now in danger of being the target of legal action. When I though it was just the example (and not the "explanation" as well), I was undecided on whether this is plagiarism or fair use. Now, I have made up my mind on how to categorize this.
I don't agree with this as credit was given in the explanation to the original source.
But again I think it is poor that I can copy anything out of BOL and be considered a contributor
October 18, 2012 at 8:17 am
In the short time I have been involved with the QotD I am amazed at discussions that go on over sometimes simple SQL examples. I guess I just looked at it as a question that I had to look up to answer but learned something in the process.
October 18, 2012 at 10:00 am
Bob -
For the most part I agree. But there are times that the question or the answer gets under my skin and I feel like I need to grump about it. Part of human nature I think.
M.
Not all gray hairs are Dinosaurs!
October 18, 2012 at 10:54 am
Would be good if MS contacted (or hire) any (or many) of the collaborators of SSC to improve (or redo from start) BOL.
So BOL will become a really complete and well documented reference. But perhaps I'll expend more time in BOL than in SSC if this happen. Hmmm... on second thought, it's better let the things as they are now.
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply