SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counting spaces


Counting spaces

Author
Message
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1797 Visits: 1814
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
Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3862 Visits: 1465
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.



Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1797 Visits: 1814
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
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2647 Visits: 2833
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.
Bhavesh_Patel
Bhavesh_Patel
Say Hey Kid
Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)

Group: General Forum Members
Points: 687 Visits: 297
thnx - got something new to learn



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
VM-723206
VM-723206
Right there with Babe
Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

Group: General Forum Members
Points: 778 Visits: 267
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 <= 10
BEGIN
INSERT INTO @temp VALUES ( SPACE(@inc)+'Hello',
SPACE(@dec)+'Hello');
SET @inc = @inc + 1;
SET @dec = @dec - 1;
END

select * from @temp

SELECT COUNT(*) FROM @temp WHERE LEN(StringA)= LEN(StringB);
Peter Trast
Peter Trast
Say Hey Kid
Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)

Group: General Forum Members
Points: 702 Visits: 655
Another lucky guess Smile

When you pipe the results out to a text file, you get:

StringA,StringB
hello ,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??

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search