Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Counting spaces Expand / Collapse
Author
Message
Posted Monday, December 7, 2009 1:28 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:33 AM
Points: 1,676, Visits: 1,760
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
Post #830236
Posted Monday, December 7, 2009 1:56 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:36 PM
Points: 2,850, Visits: 1,165
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.


Post #830260
Posted Monday, December 7, 2009 2:11 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:33 AM
Points: 1,676, Visits: 1,760
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
Post #830269
Posted Tuesday, December 8, 2009 8:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:39 PM
Points: 1,382, Visits: 1,752
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.
Post #830741
Posted Wednesday, December 9, 2009 3:43 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 11, 2013 12:41 PM
Points: 621, Visits: 297
thnx - got something new to learn



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
Post #831267
Posted Friday, December 11, 2009 7:45 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 AM
Points: 654, 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);
Post #832950
Posted Saturday, March 20, 2010 3:28 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
Another lucky guess :)

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
Post #886982
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse