Spaces in WHERE clause for SQL Server

Question

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?

https://dba.stackexchange.com/questions/10510/behavior-of-varchar-with-spaces-at-the-end — Ivan Starostin, Oct 01 '18 at 13:22
Its simple: looks into empty string. doesnt matter if its one or two spaces. try it this way 'value ' and check result set — Hadrian, Oct 01 '18 at 13:24
*If* SQL Server would adhere to Standard SQL it would be really simple:`where col like ' '` — dnoeth, Oct 01 '18 at 14:02

score 8 · Accepted Answer · answered Oct 01 '18 at 13:25

8

Yes, it ignores trailing spaces in comparisons.

You can try to append a delimiting character.

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

answered Oct 01 '18 at 13:25

sticky bit

36,626
12
31
42

Technically the standard requires the shorter string be padded with spaces until it has the same length as the longer string. In practice though, I've never found a situation where that would produce a different outcome than trimming/ignoring the trailing spaces. – Damien_The_Unbeliever Oct 01 '18 at 13:30
4

Unfortunately, this precludes the use of indexes. – Gordon Linoff Oct 01 '18 at 13:59
2

@GordonLinoff Could that be solved with `WHERE col = ' ' AND col+'X' = ' X'`? The first part is indexed, then it applies the refinement. – Barmar Oct 01 '18 at 15:48
2

@barmar . . . I would go with `col = ' ' and len(col) = 1`. – Gordon Linoff Oct 01 '18 at 18:01
@Damien_The_Unbeliever An off the cuff first thought with no experimentation: Maybe [unicode spaces](http://jkorpela.fi/chars/spaces.html)? – Izkata Oct 01 '18 at 18:11

score 3 · Answer 2 · answered Oct 01 '18 at 13:35

3

You can combine DATALENGTH clause with your query:

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

answered Oct 01 '18 at 13:35

S.K.

3,597
2
16
31

score 3 · Answer 3 · answered Oct 01 '18 at 14:07

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.

score 0 · Answer 4 · answered Oct 01 '18 at 14:20

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:

Spaces in WHERE clause for SQL Server

4 Answers4