SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ron Moses / Counting spaces / Latest Posts

RE: Counting spaces

Peter Trast — Sat, 20 Mar 2010 15:28:37 GMT

Another lucky guess :)When you pipe the results out to a text file, you get:StringA,StringBhello ,hello (one space after first hello)hello ,hello (2 spaces after first hello, etc...)hello ,hello hello ,hello hello ,hello hello ,hello hello ,hello hello ,hello hello ,hello hello ,hello with 7 spaces AFTER each second hello, interesting result... why 7 spaces??

RE: Counting spaces

VM-723206 — Fri, 11 Dec 2009 07:45:19 GMT

A variation of this question could be:DECLARE @inc int, @dec int;SELECT @inc = 1, @dec = 10;DECLARE @temp table (StringA varchar(20), StringB varchar(20));WHILE @inc <= 10BEGIN INSERT INTO @temp VALUES ( SPACE(@inc)+'Hello', SPACE(@dec)+'Hello'); SET @inc = @inc + 1; SET @dec = @dec - 1;ENDselect * from @tempSELECT COUNT(*) FROM @temp WHERE LEN(StringA)= LEN(StringB);

RE: Counting spaces

Bhavesh_Patel — Wed, 09 Dec 2009 03:43:47 GMT

thnx - got something new to learn

RE: Counting spaces

sknox — Tue, 08 Dec 2009 08:42:22 GMT

[quote][b]Scott Coleman (12/7/2009)[/b][hr]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.[/quote]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.

RE: Counting spaces

Oleg Netchaev — Mon, 07 Dec 2009 14:11:42 GMT

[quote][b]Scott Coleman (12/7/2009)[/b][hr]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.[/quote]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 [b]hello [/b]right-padded with tab character (ascii code 9) smaller than the word [b]hello [/b]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

RE: Counting spaces

Scott Coleman — Mon, 07 Dec 2009 13:56:47 GMT

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.

RE: Counting spaces

Oleg Netchaev — Mon, 07 Dec 2009 13:28:08 GMT

[quote][b]sknox (12/7/2009)[/b][hr]But in this example, SQL server isn't comparing strings [i]at all[/i]. 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.[/quote]Not exactly so. SQL server ignores trailing spaces only when calculating the length of a string [i]using the [b]len[/b] function[/i], 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,[code="sql"]select len('hello ') len_function, datalength('hello ') datalength_function[/code]will yield the following result:[code="plain"]len_function datalength_function------------ -------------------5 6[/code]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

RE: Counting spaces

sknox — Mon, 07 Dec 2009 11:24:15 GMT

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 [i]at all[/i]. 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.

RE: Counting spaces

ronmoses — Mon, 07 Dec 2009 09:11:27 GMT

[quote][b]Steve Jones - Editor (12/7/2009)[/b][hr]nice question, got me as well.[/quote]You just made my day, Mr. Jones. :-D

RE: Counting spaces

Steve Jones - SSC Editor — Mon, 07 Dec 2009 09:03:03 GMT

nice question, got me as well.I'll correct the case sensitivity

RE: Counting spaces

ronmoses — Mon, 07 Dec 2009 05:44:45 GMT

[quote][b]rcavill (12/7/2009)[/b][hr]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[/quote]I didn't even notice that capital "D"... I promise it wasn't meant to be any kind of case-sensitive trick question! :-D Maybe a mod could fix that...

RE: Counting spaces

Carlo Romagnano — Mon, 07 Dec 2009 03:28:17 GMT

[quote][b]rcavill (12/7/2009)[/b][hr]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[/quote]Have good luck for [b]"Case Sensitive collation" [/b]

RE: Counting spaces

rcavill — Mon, 07 Dec 2009 01:35:27 GMT

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

RE: Counting spaces

rajaneeshk — Sun, 06 Dec 2009 23:16:29 GMT

Yeah... That was a good one... made to think for a while....:-)

RE: Counting spaces

TroyG — Sun, 06 Dec 2009 19:58:28 GMT

yeah nice curve ball :)

RE: Counting spaces

Ol'SureHand — Sun, 06 Dec 2009 16:10:01 GMT

Excellent trick question.String comparison for [b]both char & varchar[/b] ignores trailing spaces - even when ANSI_PADDING is ON.

Counting spaces

ronmoses — Sat, 05 Dec 2009 11:26:18 GMT

Comments posted to this topic are about the item [B]Counting spaces[/B]