Len() with different datatypes

Question

Len() with different datatypes

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

psingla Hall of Fame Points: 3840 More actions · Answer 1

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/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

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.

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/

Anipaul SSC-Insane Points: 24681 More actions · Answer 3

Anipaul

SSC-Insane

Points: 24681

July 9, 2013 at 3:08 am

#1630690

Nice one....

bsk1234 Grasshopper Points: 11 More actions · Answer 4

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...............

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

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 '

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/

bsk1234 Grasshopper Points: 11 More actions · Answer 6

Thank you, Hugo! I appreciate the clarification. It was quick too... 🙂

jlennartz SSCommitted Points: 1574 More actions · Answer 7

bsk1234 (7/9/2013)
Thank you, Hugo! I appreciate the clarification. It was quick too... 🙂

+1

Hany Helmy SSChampion Points: 13921 More actions · Answer 8

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

jacquereed SSC Journeyman Points: 99 More actions · Answer 9

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?

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 10

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

sqlnaive SSCoach Points: 17437 More actions · Answer 11

sqlnaive

SSCoach

Points: 17437

July 22, 2013 at 3:01 am

#1634370

Good simple question. 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

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