December 5, 2009 at 11:26 am
Comments posted to this topic are about the item Counting spaces
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
December 6, 2009 at 4:10 pm
Excellent trick question.
String comparison for both char & varchar ignores trailing spaces - even when ANSI_PADDING is ON.
December 6, 2009 at 7:58 pm
yeah nice curve ball 🙂
December 6, 2009 at 11:16 pm
Yeah... That was a good one... made to think for a while....:-)
December 7, 2009 at 1:35 am
You need the option
Must declare the scalar variable "@Dec".
When running on a Case Sensitive collation.
Once that select statement on line 2 is adjusted then it would work.
------
Robert
December 7, 2009 at 3:28 am
rcavill (12/7/2009)
You need the optionMust declare the scalar variable "@Dec".
When running on a Case Sensitive collation.
Once that select statement on line 2 is adjusted then it would work.
------
Robert
Have good luck for "Case Sensitive collation"
December 7, 2009 at 5:44 am
rcavill (12/7/2009)
You need the optionMust declare the scalar variable "@Dec".
When running on a Case Sensitive collation.
Once that select statement on line 2 is adjusted then it would work.
------
Robert
I didn't even notice that capital "D"... I promise it wasn't meant to be any kind of case-sensitive trick question! 😀 Maybe a mod could fix that...
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
December 7, 2009 at 9:03 am
nice question, got me as well.
I'll correct the case sensitivity
December 7, 2009 at 9:11 am
Steve Jones - Editor (12/7/2009)
nice question, got me as well.
You just made my day, Mr. Jones. 😀
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
December 7, 2009 at 11:24 am
Technically speaking, the explanation for the answer is not correct. The explanation given is that SQL server ignores trailing spaces when comparing strings.
But in this example, SQL server isn't comparing strings at all. It's comparing integer values (the length of each string.)
The correct explanation is that SQL server ignores trailing spaces when calculating the length of a string.
December 7, 2009 at 1:28 pm
sknox (12/7/2009)
But in this example, SQL server isn't comparing strings at all. It's comparing integer values (the length of each string.)The correct explanation is that SQL server ignores trailing spaces when calculating the length of a string.
Not exactly so. SQL server ignores trailing spaces only when calculating the length of a string using the len function, which is not the only one to calculate the lenght of a string. If you need to account for the trailing spaces you can use the datalength function instead as the latter does not ignore the trailing spaces. For example,
select len('hello ') len_function, datalength('hello ') datalength_function
will yield the following result:
len_function datalength_function
------------ -------------------
5 6
Since the QoD specified len, the one ignoring the spaces, the answer is 10. If the datalength was used insead, the answer would be 0.
Very nice question, really liked it!
Oleg
December 7, 2009 at 1:56 pm
The LEN() function ignores trailing spaces, comparing the strings doesn't actually ignore trailing spaces but it pads the shorter string with spaces. The answer's the same either way.
December 7, 2009 at 2:11 pm
Scott Coleman (12/7/2009)
The LEN() function ignores trailing spaces, comparing the strings doesn't actually ignore trailing spaces but it pads the shorter string with spaces. The answer's the same either way.
The QoD does not include any string comparisons, just their respective lengths using the len function, the one ignoring the trailing spaces. This is true that ANSI specs call for right-padding the shorter of the 2 strings to compare with spaces until both have the equal length. Then the algorithm comparing the two kicks in. This make the string 'hello' and 'hello ' equal and the word hello right-padded with tab character (ascii code 9) smaller than the word hello itself, because to compare them the trailing space is added to the shorter and the ascii code for space (hex 20 or 32 decimal) is bigger. This might look counter intuitive (how can a longer string with the same base be smaller) but this is by design.
Oleg
December 8, 2009 at 8:42 am
Scott Coleman (12/7/2009)
The LEN() function ignores trailing spaces, comparing the strings doesn't actually ignore trailing spaces but it pads the shorter string with spaces. The answer's the same either way.
In this case, the answer's the same, but it's important not to get the right answer for the wrong reason. Specifically, it's important to know that the code in question is not comparing strings, because although using the len() function will match "hello" and "hello ", it will also match "hello" and "jello ", or any two strings, no matter their content, if their calculated length is the same.
December 9, 2009 at 3:43 am
thnx - got something new to learn
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply