> В пустой строке нет пробелов, пустая строка есть в любой (не нулевой) строке.

ненене!
поиск пустой строки в строке - это шаблон '%%', а не ''.
а то, что для varchar не учитываются концевые
пробелы в строке, для которой проверяется шаблон, это
надо просто запомнить.

для Nvarchar, кстати, учитываются.



Posted via ActualForum NNTP Server 1.5

> не могу сформулировать принцип действия :(

для сравнения с помощью = концевые пробелы не учитываются никогда.
в BOL можно найти здесь
http://msdn.microsoft.com/en-us/library/ms191529(v=sql.105).aspx
BOLTrailing blanks are ignored in comparisons; for example, these are equivalent:

но, собственно, это и требование стандарта (сформулировано несколько иначе, но результат тот же):
<comparison predicate>

If the length in characters of X is not equal to the length in characters of Y, then the shorter string is
effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to
the length of the longer string by concatenation on the right of one or more pad characters, where the
pad character is chosen based on CS. If CS has the NO PAD characteristic, then the pad character is
an implementation-dependent character different from any character in the character set of X and Y
that collates less than any string under CS. Otherwise, the pad character is a <space>.


для like расписано в статье про like.
http://msdn.microsoft.com/en-us/library/ms179859.aspx
BOLWhen you perform string comparisons by using LIKE, all characters in the pattern string are significant. This includes
leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by
a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing
blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows
with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are
returned.

....

LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and
escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any one of the
arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed.
When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for
non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the ISO standard. ASCII LIKE is
compatible with earlier versions of SQL Server.


Posted via ActualForum NNTP Server 1.5