Spaces in WHERE clause for SQL Server [duplicate]
Yes, it ignores trailing spaces in comparisons.
You can try to append a delimiting character.
SELECT count(*)FROM mytableWHERE col + 'X' = ' X';
You can combine DATALENGTH clause with your query:
select COUNT(*) from mytable where col = ' ' and DATALENGTH(col) = 1
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 mytablewhere col LIKE ' ' -- one spaceAnd 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.