SQL Server 2008 Пустая строка против пространства
Сегодня утром я столкнулся с чем-то странным и подумал, что отправлю это для комментариев.
Может кто-нибудь объяснить, почему следующий SQL-запрос выдает "равный" при работе с SQL 2008. Уровень совместимости БД установлен на 100.
if '' = ' '
print 'equal'
else
print 'not equal'
И это возвращает 0:
select (LEN(' '))
Кажется, это автоматическая обрезка пространства. Я понятия не имею, так ли это было в предыдущих версиях SQL Server, и у меня больше нет возможности даже протестировать его.
Я столкнулся с этим, потому что производственный запрос возвращал неверные результаты. Я не могу найти это поведение нигде документально.
У кого-нибудь есть информация по этому поводу?
9 ответов
varchars и равенство тернисты в TSQL. LEN функция говорит:
Возвращает количество символов, а не количество байтов данного строкового выражения, за исключением конечных пробелов.
Вам нужно использовать DATALENGTH чтобы получить правду byte количество данных данных. Если у вас есть данные Unicode, обратите внимание, что значение, которое вы получаете в этой ситуации, не будет таким же, как длина текста.
print(DATALENGTH(' ')) --1
print(LEN(' ')) --0
Когда дело доходит до равенства выражений, две строки сравниваются на равенство следующим образом:
- Получить более короткую строку
- Подушка с пробелами, пока длина не будет равна длине более длинной струны
- Сравните два
Это средний шаг, который вызывает неожиданные результаты - после этого вы фактически сравниваете пустое пространство с пустым пространством - следовательно, они рассматриваются как равные.
LIKE ведет себя лучше, чем = в ситуации "заготовок", потому что он не выполняет заполнение пробелов в шаблоне, который вы пытаетесь сопоставить:
if '' = ' '
print 'eq'
else
print 'ne'
Дам eq в то время как:
if '' LIKE ' '
print 'eq'
else
print 'ne'
Дам ne
Осторожнее с LIKE хотя: он не является симметричным: он рассматривает конечные пробелы как значимые в шаблоне (RHS), но не как выражение соответствия (LHS). Следующее взято отсюда:
declare @Space nvarchar(10)
declare @Space2 nvarchar(10)
set @Space = ''
set @Space2 = ' '
if @Space like @Space2
print '@Space Like @Space2'
else
print '@Space Not Like @Space2'
if @Space2 like @Space
print '@Space2 Like @Space'
else
print '@Space2 Not Like @Space'
@Space Not Like @Space2
@Space2 Like @Space
Оператор = означает, что T-SQL не столько "равен", сколько "является одним и тем же словом / фразой в соответствии с сопоставлением контекста выражения", а LEN - "количеством символов в слове / фразе". Никакие сопоставления не рассматривают завершающие пробелы как часть слова / фразы, предшествующих им (хотя они действительно обрабатывают начальные пробелы как часть строки, которой они предшествуют).
Если вам нужно отличить "это" от "этого", вам не следует использовать оператор "это одно и то же слово или фраза", потому что "это" и "это" - это одно и то же слово.
Вклад в способ = работы заключается в том, что оператор равенства строк должен зависеть от содержимого своих аргументов и контекста сопоставления выражения, но он не должен зависеть от типов аргументов, если они оба являются строковыми типами,
Понятие "это одно и то же слово" на естественном языке, как правило, недостаточно точное, чтобы его можно было уловить с помощью математического оператора, такого как =, и в естественном языке нет понятия строкового типа. Контекст (т. Е. Сопоставление) имеет значение (и существует на естественном языке) и является частью истории, а дополнительные свойства (некоторые из которых кажутся странными) являются частью определения =, чтобы сделать его четко определенным в неестественном мире данные.
Что касается вопроса о типах, вы не хотите, чтобы слова менялись, когда они хранятся в разных типах строк. Например, типы VARCHAR(10), CHAR(10) и CHAR(3) могут содержать представления слова 'cat' и? = 'кошка' должна позволить нам решить, содержит ли значение любого из этих типов слово 'кошка' (с учетом регистра и акцента, определяемых сопоставлением).
Ответ на комментарий JohnFx:
См. Использование данных char и varchar в электронной документации. Цитирую с этой страницы, акцент мой:
Каждое значение данных char и varchar имеет параметры сортировки. Параметры сортировки определяют такие атрибуты, как битовые комбинации, используемые для представления каждого символа, правила сравнения и чувствительность к регистру или акценту.
Я согласен, что это может быть легче найти, но это задокументировано.
Стоит также отметить, что семантика SQL, где = имеет отношение к реальным данным, и контекст сравнения (в отличие от чего-то относительно битов, хранящихся на компьютере) уже долгое время является частью SQL. Предпосылкой СУБД и SQL является точное представление данных реального мира, следовательно, его поддержка сопоставлений за много лет до того, как подобные идеи (такие как CultureInfo) вошли в сферу языков, подобных Алголу. Предпосылкой этих языков (по крайней мере, до недавнего времени) было решение технических проблем, а не управление бизнес-данными. (В последнее время использование похожих языков в неинженерных приложениях, таких как поиск, делает некоторые успехи, но Java, C# и т. Д. Все еще борются со своими некоммерческими корнями.)
На мой взгляд, несправедливо критиковать SQL за то, что он отличается от "большинства языков программирования". SQL был разработан для поддержки среды моделирования бизнес-данных, которая сильно отличается от разработки, поэтому язык отличается (и лучше для своей цели).
Черт, когда SQL был впервые указан, некоторые языки не имели встроенного строкового типа. И все же в некоторых языках оператор равенства между строками вообще не сравнивает символьные данные, а сравнивает ссылки! Меня не удивит, если через пару десятилетий идея о том, что == зависит от культуры, станет нормой.
Я нашел эту статью в блоге, которая описывает поведение и объясняет почему.
Стандарт SQL требует, чтобы сравнение строк эффективно заполняло более короткую строку пробелами. Это приводит к удивительному результату, что N'' = N' ' (пустая строка равна строке из одного или нескольких пробельных символов), а в более общем случае любая строка равна другой строке, если они отличаются только конечными пробелами. Это может быть проблемой в некоторых контекстах.
Более подробная информация также доступна в MSKB316626
Был такой же вопрос некоторое время назад, когда я смотрел на подобную проблему здесь
Вместо LEN(' ') используйте DATALENGTH(' ') - это даст вам правильное значение.
Решением было использовать предложение LIKE, как описано в моем ответе, и / или включить второе условие в предложение WHERE, чтобы также проверить DATALENGTH.
Прочитайте этот вопрос и ссылки там.
Чтобы сравнить значение с литеральным пространством, вы также можете использовать эту технику в качестве альтернативы оператору LIKE:
IF ASCII('') = 32 PRINT 'equal' ELSE PRINT 'not equal'
Как предикат сравнения SQL - 92 8.2, говорящий:
Если длина в символах X не равна длине в символах Y, то более короткая строка эффективно заменяется для целей сравнения копией самой себя, которая была расширена до длины более длинной строки путем конкатенации. справа от одного или нескольких вспомогательных символов, где вспомогательный символ выбирается на основе CS. Если CS имеет атрибут NO PAD, то символ заполнения является зависящим от реализации символом, отличным от любого символа в наборе символов X и Y, который упорядочивает меньше, чем любая строка в CS. В противном случае символ пэда является
<space>.
Иногда приходится иметь дело с пробелами в данных, с другими символами или без них, хотя идея использования Null лучше, но не всегда пригодна для использования. Я столкнулся с описанной ситуацией и решил ее так:
... где ('>' + @space + '<') <> ('>' + @ space2 + '<')
Конечно, вы не будете делать этот большой объем данных, но он работает быстро и легко для нескольких сотен строк...
Герберт
Другой способ - вернуть его в состояние, в котором пространство имеет ценность. Например: заменить пробел на символ, известный как _
if REPLACE('hello',' ','_') = REPLACE('hello ',' ','_')
print 'equal'
else
print 'not equal'
возвращает: не равно
Не идеально и, вероятно, медленно, но это еще один быстрый путь вперед, когда это необходимо быстро.
Как различить записи по select с полями char/varchar на сервере sql: пример:
declare @mayvar as varchar(10)
set @mayvar = 'data '
select mykey, myfield from mytable where myfield = @mayvar
ожидаемый
mykey (int) | Myfield (varchar10)
1 | 'данные '
полученный
mykey | MyField
1 | "данные" 2 | 'данные '
даже если я напишу select mykey, myfield from mytable where myfield = 'data' (без окончательного пробела) Я получаю те же результаты.
как я решил? В этом режиме:
select mykey, myfield
from mytable
where myfield = @mayvar
and DATALENGTH(isnull(myfield,'')) = DATALENGTH(@mayvar)
и если на myfield есть индекс, он будет использоваться в каждом случае.
Я надеюсь, что это будет полезно.