July 8, 2013 at 8:11 pm
Hugo Kornelis (7/8/2013)
Not a bad question, but the explanation is only partial. Here's a fuller explanation.For varchar, trailing spaces are preserved, so the variable stores six letters plus two trailing spaces. For LEN, trailing spaces are ignored (as indicated in the explanation), so 6 is returned. But REVERSE operates on the entire string, yielding two leading spaces plus six letters. Leading spaces are not ignored for LEN - result = 8.
For char, values are padded with spaces to the specified length, so the variable stores six letters plus six trailing spaces. For LEN, trailing spaces are ignored (as indicated in the explanation), so 6 is returned. But REVERSE operates on the entire string, yielding six leading spaces plus six letters. Leading spaces are not ignored for LEN - result = 12.
Unlike Tom, I am not really happy that there was only one answer with the correct option for LEN(REVERSE(@Char)) - made it too easy. His point about counting spaces would have been valid if there had been answer options like 7, 8, and 9 - but that is not the case. From the fact that the only numbers in the answer options were 6 (no trailing spaces), 12 (max length) and 8, it was clear that there were two trailing spaces in the string constants.
Good Question and explanation
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
July 9, 2013 at 3:08 am
logitestus (7/8/2013)
This is my 2 cents, but I searched through BOL (my usual default starting location for answering TSQL questions) on both LEN and REVERSE and neither helped in the "correct" answer department. I also dug into the data types area and I could find no information as to why a LEN reverse on a char field would yield the length of the entire field.Am I missing some important research location? The only reason why I got a "correct" answer is that I ran the query, my incorrect answer would have been 6,6,8,8 and that is based off the documentation (unless I am reading it incorrectly).
All the information is in Books Online - you just need to find it. (And I do not have the time to dig for all locations right now).
I have already posted an explanation of exactly what is happening, earlier in the topic. Please read that first. After that, if you still have questions, feel free to let us know which part is unclear and people here will explain.
July 9, 2013 at 3:08 am
Nice one....
July 9, 2013 at 5:47 am
I know author's intention of highlighting the space taken by the two data types; and I appreciate it. However, when I ran the query EXACTLY as given by the author, this is the result .....
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@Vchar".
Was some proof-read necessary? Is the query specific to a particular version? Grrrrrrrrrrrrrrrr...............
July 9, 2013 at 6:04 am
bsk1234 (7/9/2013)
I know author's intention of highlighting the space taken by the two data types; and I appreciate it. However, when I ran the query EXACTLY as given by the author, this is the result .....Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@Vchar".
You must be running SQL Server 2005, or earlier. Thehse versions are already out of Microsoft support, and for the Question of the Day the general assumption is that they apply to versions currently (as of publishing date) supported versions, unless otherwise specified.
The problem is in assigning a value to a variable in the declaration statement, valid since SQL Server 2008. For your version, try changing
declare @Vchar varchar(12)='prasad '
, @Char char(12)='prasad '
to
declare @Vchar varchar(12)
, @Char char(12)
select @Vchar ='prasad '
, @Char ='prasad '
July 9, 2013 at 6:13 am
Thank you, Hugo! I appreciate the clarification. It was quick too... 🙂
July 10, 2013 at 2:15 pm
bsk1234 (7/9/2013)
Thank you, Hugo! I appreciate the clarification. It was quick too... 🙂
+1
July 11, 2013 at 1:59 pm
Thank you Hugo, I was about to ask a question regarding this particular point (LEN(REVERSE(@Char))) but then I found your post, so no need.
Warm Regards,
Hany Helmy
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
July 15, 2013 at 11:33 am
I put down the answer 6,6,8,6 and it said I got it wrong, but when I ran it through my sql server, it put out exactly that -- What's up?
July 15, 2013 at 11:46 pm
jacquereed (7/15/2013)
I put down the answer 6,6,8,6 and it said I got it wrong, but when I ran it through my sql server, it put out exactly that -- What's up?
have a look at my reply http://www.sqlservercentral.com/Forums/FindPost1471085.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 22, 2013 at 3:01 am
Good simple question. 🙂
July 25, 2013 at 9:59 am
L' Eomot Inversé (7/7/2013)
Nice easy question, although at first site it looks rather unpleasant.There are too many questions which would be easy if it was easy to see how many blanks there are. Questions that expect us to be able to count blanks (difficult when looking at HTML, so go look at the hex coding of the html on the QotD page or copy the code into notepad or something else that lets one count spaces easily). This question isn't like that, because only one of the answer options has 12 as the last number, and as that is obviously required it immediately delivers the answer (and then we can see from the rest of the answer that the other string had two trailing spaces - no need for any counting). so this is a good question that looks a bit like quite a few bad ones.
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply