SELECT 'Equal'
WHERE ' ' = ''
Result:
-----
Equal
(1 row(s) affected)
Asked
Active
Viewed 105 times
0
Phil Gan
- 2,813
- 2
- 29
- 38
-
1What are you having trouble figuring out? – Joe Phillips Apr 07 '11 at 15:03
-
string comparison is affected by a lot of things, including language. maybe your locale settings are not what you expect? – drysdam Apr 07 '11 at 15:04
2 Answers
7
SQL Server does not consider trailing spaces when comparing strings using the = operator. That's why it considers ' ' to be equivalent to ''.
Here's a page that explains the semantics: http://support.microsoft.com/kb/316626
Dr. Wily's Apprentice
- 10,212
- 1
- 25
- 27
-
They don't seem to count in the `LEN` function either. Seems like an odd decision to make. – Phil Gan Apr 07 '11 at 15:08
-
1@Phil I think I can understand why the `LEN` function is implemented that way. Imagine that you're using it on a char(5) field. Since it's fixed length, if you considered trailing spaces, then `LEN` would always return 5 for that field, which isn't very useful. I imagine that 99% of the time you wouldn't/shouldn't care about trailing whitespace. I think that if code is depending on trailing spaces stored in the database, then I would really question why that is the case. – Dr. Wily's Apprentice Apr 07 '11 at 15:15
0
SQL Server evaluated ' ' = '' as a boolean type that equated to TRUE, therefore it will return the string literal that you specified in your SELECT.
The same would occur with: SELECT 'Equal' WHERE 1=1
Quantum Elf
- 752
- 4
- 9
-
1
-
Exactly, I could understand if the left and right sides of the equality were the same. – Phil Gan Apr 07 '11 at 15:22