Spaces in WHERE clause for SQL Server [duplicate] Spaces in WHERE clause for SQL Server [duplicate] sql sql

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 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.