mjhjmtu

I want to find out the records in which a certain column contains exactly one space and nothing else. So I wrote the first of the following queries:

select COUNT(*)
from mytable
where col = ' ' -- One space

select COUNT(*)
from mytable
where col = ' ' -- Two spaces

select COUNT(*)
from mytable
where col = ' ' -- Three spaces

However, all three queries return the exact same records. Does Microsoft SQL Server not distinguish between the amount of spaces? How can I query exactly for one, two or more spaces?

Yes, it ignores trailing spaces in comparisons.

You can try to append a delimiting character.

SELECT count(*)
FROM mytable
WHERE col + 'X' = ' X';

The link posted by Ivan Starostin in the comments of the OP provides a good explanation and I think it deserves a full answer instead of just a comment.

To summarize, try using LIKE instead of equality:

select COUNT(*)
from mytable
where col LIKE ' ' -- one space

And you can also use DATALENGTH to calculate how many bytes are in the field to double-check field length:

select col, DATALENGTH(col)
from mytable;

Please note that DATALENGTH will return a different value if col is a VARCHAR vs NVARCHAR. VARCHAR stores each character as 1 byte where NVARCHAR stores each character as 2 bytes since NVARCHAR is stored in Unicode.

You can combine DATALENGTH clause with your query:

 select COUNT(*)
 from mytable
 where col = ' '
 and DATALENGTH(col) = 1

You can replace the single space with a single character (for exampe §) and then put this character in your where condition:

declare @tmp table(col varchar(50))

insert into @tmp values
(' '),
(' '),
(' ')

select COUNT(*) as one_space_count
from @tmp
where replace(col,' ','§')='§'

select COUNT(*) as two_space_count
from @tmp
where replace(col,' ','§')='§§'

select COUNT(*) as three_space_count
from @tmp
where replace(col,' ','§')='§§§'

Results: