1

I just realised, the following behaviour of SQL Server

SELECT
    1
WHERE
    ' ' = '        '

Seemingly the string with just 1 space equals the string with 8 spaces. Can anyone explain, why that is and how i can compare empty strings?

AffenkingLouie
  • 21
  • 1
  • 3
    Try to read this http://stackoverflow.com/questions/1399844/sql-server-2008-empty-string-vs-space – Sk1X1 Apr 15 '14 at 11:51
  • 1
    Voting to close - yes, essentially same question and the answer answers this perfectly. – TomTom Apr 15 '14 at 11:59

2 Answers2

0

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations

use DataLength to compare in where clause.

http://msdn.microsoft.com/en-in/library/ms173486.aspx

Chethan
  • 298
  • 1
  • 4
  • 17
-1

To compare for empty string

SELECT *
FROM tableName
WHERE LTRIM(RTRIM(columnName))=''
Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
  • 1
    @downvoter plz explain wrong approach and what should be better way.. – Mudassir Hasan Apr 15 '14 at 12:02
  • did you try what you posted? have a table with a varchar column and insert `SELECT ' ' UNION ALL SELECT ' ' UNION ALL SELECT ''` your select will still get all those three values back – DrCopyPaste Apr 15 '14 at 12:25