STR
-
I answered the QotD based on how I received it (STR(123.45, 2, 2)) via email. The Webpage currently lists a different statement (iirc, STR(987.65, 2, 3)) -- please correct it, as the answer choices reflect the emailed statement. Thanks.
-
mbova407 (10/18/2012)
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)
The trouble with out of context quotes is that they can be utterly misleading. The sentence in BoL assumes that you know what "the expression exceeds the specified length" means, which is explained somewhere further up the page. Without that bit further up the page, there are two things that people might assume it means: (i) the length of the expression is longer than the length parameter, which seems at first sight quite a reasonable guess but only makes sense with a definition of "the length of the expression", and since no definition is supplied people would probably assume to be, for example , 4 for 1.23, 5 for -1.23, and so on which is also a reasonable assumption but certainly incorrect; (ii) the value of the expression is greater than the specified length, which is neither a reasonable assumption nor correct. What it actually means is that the length parameter specifies enough to hold the amount of teh expression that would, in a full representation, be to the left of the decimal point (don't forget that that length depends not only on the sign but sometimes also on whether rounding of truncation is used (which in turn, in the cases where it matters, depends on whether the expression's value is greater than -1014 and less than 1015 or not (if I've remembered all this stuff correctly - those may not be the right bounds).
Copying a piece of the page that is meaningless out of context makes an awfully bad explanation (the BoL page is, as Hugo points out, is pretty awful too, but it least the text further up the page prepares you for the somewhat bizarre sentence quoted).
edit: fix marks
Tom
-
Hugo Kornelis (10/18/2012)
(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!)I wish the site owners would get this fixed. It rarely catches me now, as I usually remember to copy to clipboard before hitting the "Post quoted reply". Maybe the "quote the wrong message" bug could be fixed at the same time.
But I don't think it'll happen.
Tom
-
Michael Poppers (10/18/2012)
I answered the QotD based on how I received it (STR(123.45, 2, 2)) via email. The Webpage currently lists a different statement (iirc, STR(987.65, 2, 3)) -- please correct it, as the answer choices reflect the emailed statement. Thanks.That explains it. I was kind of fuzzy as to what STR was or what I would use it for, but reading BOL I was still a bit baffled, except that none of the answers had the same digits as the question, so the ** was the only choice I could make.
The answers not matching the question left me even more confused, but I understand now. Heh
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
Thanks Ravi....was new for me. Was not aware that result can be "**" 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Thanks for the question. Never used the STR function before, and probably never will.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
cfradenburg (10/18/2012)
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.STR will add trailing zeros to the specified precision, and it will right-align the numbers. CONVERT has no trainilg zeros and left-aligns. So you would use STR if you want this output:
12300.001.23
12.30
And you'd use CAST or CONVERT if you want the result to look like this:
123001.23
12.3
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Thanks, Hugo.
-
Cool One. +1
-
Nice one, thanks
Iulian
-
Michael Poppers (10/18/2012)
I answered the QotD based on how I received it (STR(123.45, 2, 2)) via email. The Webpage currently lists a different statement (iirc, STR(987.65, 2, 3)) -- please correct it, as the answer choices reflect the emailed statement. Thanks.I got it right based on the website question - the three wrong answers bear absolutely no resemblance to the source number, so can't possibly be right.
I'm guessing someone (Steve?) changed the question to differ from BOL, but forgot to change the answers too.
-
no trick, no controverse just knowledge!
thank s
-
Yes, my question is different from the one posted on the site. I think it is Steve who changed the question.
Regards,
Ravi.
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply