Подобно 1с не в запросе 1с
Рассмотрим назначение и использование условного оператора ПОДОБНО(eng. LIKE) в языке запроса 1С в примерах.
Предназначение
Проверить на соответствие строкового значения в запросе указанному шаблону — возвращает значение Булево-типа (ИСТИНА или ЛОЖЬ).
- Проверка регистро — независима.
- При запросе используются индексы таблиц — не связано с индексами полнотекстового поиска.
- Может долго выполнятся при больших таблицах.
- Строки неограниченной длины следует привести функцией ПОДСТРОКА
Места использования
- В условиях оператора ГДЕ
- В условиях конструкции ВЫБОР КОГДА <> ТОГДА «» ИНАЧЕ «» КОНЕЦ
- В полях выборки (например: Наименование ПОДОБНО &ПараметрПодобно как СтрокаПодходит)
Описание синтаксиса оператора ПОДОБНО
Параметр оператора должен быть строкой: может задаться константой, либо передан как параметр запроса.
Литералы(маски), указанные ниже, допускается использовать совместно и по отдельности.
Точное указание строки
ВЫБРАТЬ первые 10
Ключи.Наименование
ИЗ
Справочник.Ключи КАК Ключи
ГДЕ
Ключи.Наименование ПОДОБНО "1"//Равносильно Ключи.Наименование ="1"
Результат:
% — литерал, означающий произвольное количество любых символов
ВЫБРАТЬ первые 10
Ключи.Наименование
ИЗ
Справочник.Ключи КАК Ключи
ГДЕ
Ключи.Наименование ПОДОБНО "%"
Результат: любые 10 наименований
_ (подчеркивание): литерал, соответствующий одному любому символу
Пример №1:
ВЫБРАТЬ первые 10
Ключи.Наименование
ИЗ
Справочник.Ключи КАК Ключи
ГДЕ
Ключи.Наименование ПОДОБНО "_"
Пример №2: начинающиеся на любой символ, затем следует «1», а дальше любые символы
ВЫБРАТЬ первые 10
Ключи.Наименование
ИЗ
Справочник.Ключи КАК Ключи
ГДЕ
Ключи.Наименование ПОДОБНО "_1%"
Результат:
[] (в квадратных скобках один или несколько символов)
- Каждый литерал, соответствующий одному любому символу — используется как ИЛИ.
Допустимо указание диапазона, например a-z,0-5, означающее произвольный символ из заданного диапазона
Пример
ВЫБРАТЬ первые 10
Ключи.Наименование
ИЗ
Справочник.Ключи КАК Ключи
ГДЕ
Ключи.Наименование ПОДОБНО "[лз]%"
Результат: 10 начинающихся на «л» или «з»
Пример: начинающиеся на 5,6,7
ВЫБРАТЬ первые 10
Ключи.Наименование
ИЗ
Справочник.Ключи КАК Ключи
ГДЕ
Ключи.Наименование ПОДОБНО "7%"
Результат:
[^] (в квадратных скобках значок исключения ^, за которым следует один или несколько символов)
Равносильно любому символу (_) кроме указанных ([])
Пример
ВЫБРАТЬ первые 10
Ключи.Наименование
ИЗ
Справочник.Ключи КАК Ключи
ГДЕ
Ключи.Наименование ПОДОБНО "8.[^012]%"//не включаем 8.0,8.1,8.2
Результат: все начинающиеся на «8.» исключая указанные
Пример:
Результат:
Применимость в платформах
Неверные параметры ПОДОБНО >
- Передается параметр не строкового типа: например число 1 вместо строки «1»
- Сравнивается поле не строкового типа с верной маской (например ссылка) или при соединении значение не проверяется на ЕстьNUL
Обратите внимание на текст ошибки, где выводится вопрос:
>Ключи.Ссылка ПОДОБНО "1"
Ключи.Наименование ПОДОБНО >&L
Об идеалах нации можно судить по ее рекламе.
— Норман Дуглас
(7) все работает! А пустой запрос у вас потому что вы запутались в параметрах, которых слишком много.
Зачем столько?
Сначала в консоли запросов отладьте свой запрос и увидите , что все работает и (НЕ ПОДОБНО) и НЕ (ПОДОБНО)
Непонятно почему у меня другие конструкции не работают. Ведь должны бы, вроде.
(12)похоже, что в результат не попадают строки, в которых не заполнен способ отражения. Но я же ставлю условие не выбирать только 91 и 71.. Почему не выбирает пустое?
(18)т.е я никак не могу учесть возможность выбора строки с нулевым значением СпособаОтражения? Ведь могут быть и заполненные и пустые, а нужны все.
Вы сначала выведите в запросе
и увидите , что там вообще, скажем первые 100, а уж потом ставьте условия с правильным заполнением ("Сч91", "[^9][^1]%".
Ведь в ОтражениеЗарплатыВБухучетеНачисленнаяЗарплатаИВзносы.СпособОтраженияЗарплатыВБухучете.Наименование хранятся не номера счетов!
А вот это например "Отражение начислений по умолчанию"
Ну и кроме того очень не понятно, зачем столько параметров у запроса?
Они явно лишние.
(8) Ох. Думала над чем-то подобным, но сейчас это сложный запрос для меня. Но похоже пришло время разобраться с этим. Благодарю. Буду копать..
(14) у меня так не работает. Ума не дам почему.. Работает вот такая конструкция
Блин, не работает. Конструкция вытаскивает только значения, где Наименование Способ отражения начинается на 71 и 91 ((( Ну что не так-то.
ВидОперации СпособОтраженияЗарплатыВБухучете Ссылка
1. Компенсация ежегодного отпуска Отражение зарплаты в бухучете от 28.08.2019
2. Начислено Отражение зарплаты в бухучете от 28.08.2019
3. Доходы в натуральной форме 91.2 Доход в натуральной форме Отражение зарплаты в бухучете от 28.08.2019
4. Начислено Отражение зарплаты в бухучете от 28.08.2019
5. Начислено 71.01 Возмещение подотчетных сумм Отражение зарплаты в бухучете от 28.08.2019
6. Начислено 91.2 Субсидия Отражение зарплаты в бухучете от 28.08.2019
7. Начислено Отражение зарплаты в бухучете от 28.08.2019
8. Начислено Отражение зарплаты в бухучете от 28.08.2019
Вот из этой таблицы нужно вытащить строки с видом операций Компенсация, Начислено, Начислено(сдельно) и Отпуск, не трогая другие виды вообще. Из выбранных нужно исключить строки, в которых Способ отражения начинается на 91 и 71 (использовать точно не могу, т.к много таких способов)
Суть вопроса - почему не работает мой запрос? Нужно в итоге получить 1,2,3,7 и 8 строки. с моей конструкцией все наоборот, только 5 и 6 строки(((
Среди прочих инструментов, предоставляемых механизмом запросов 1С (который, как мы знаем, есть насадка-транслятор на механизмы СУБД), имеется единственный в своём роде - наложение условия шаблоном. Большинство операций реляционных СУБД работают с точными значениями или их диапазонами, и только ПОДОБНО имеет дело с маской, нечётким условием. "ПОДОБНО", он же "LIKE", есть в подавляющем большинстве современных СУБД (согласно стандарту ANSI SQL 2003), и 1С предоставляет нам возможности такого поиска единообразно, приводя средствами платформы различные тонкости СУБД к единому поведению. Возможность эта заложена в механизме запросов, отборов, отборов СКД (во всех её проявлениях), и, как показывает практика, "ПОДОБНО" действительно ведёт себя совершенно одинаково и ожидаемо и в динамических списках, и в разных запросах - везде в 1С.
Нам известны более кастомизированные механизмы - отборы и отборы СКД "Содержит"/"Не содержит". По сути они реализованы тем же образом, что и ПОДОБНО, но урезаны, и формируют строку шаблона самостоятельно (дописывают "%"). Тому есть несколько причин, от защиты ключевых мест системы от действий пользователя и разработчика, провоцирующих избыточную нагрузку, до простоты реализации в платформе.
Нам известны более гибкие механизмы - это полнотекстовый поиск. Он в СУБД реализуется отдельно от "основного движка", требует свои конструкции, индексы, и "может всё" ценой накладных расходов на разработку и поддержание. Известны также регулярные выражения и т.д.
Поиск "ПОДОБНО" опирается на штатную работу СУБД, на обращения к колонкам фактографической реляционной БД, пусть даже они строкового типа неограниченной длины. Обработка отбора "ПОДОБНО" обычно не требует специальных донастроек ни в 1С, ни в СУБД.
Оператор ПОДОБНО позволяет сравнить значение выражения, указанного слева от него, со строкой шаблона, указанной справа. Значение выражения должно иметь тип строка. Если значение выражения удовлетворяет шаблону – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ.
Следующие символы в строке шаблона являются служебными и имеют смысл, отличный от символа строки:
% (процент): последовательность, содержащая любое количество произвольных символов.
_ (подчеркивание): один произвольный символ.
[…] (в квадратных скобках один или несколько символов): любой одиночный символ из перечисленных внутри квадратных скобок.
В перечислении могут встречаться диапазоны, например a-z, означающие произвольный символ, входящий в диапазон, включая концы диапазона.
[^…] (в квадратных скобках значок отрицания, за которым следует один или несколько символов): любой одиночный символ, кроме тех, которые перечислены следом за значком отрицания.
Любой другой символ означает сам себя и не несет никакой дополнительной нагрузки.
Если в качестве самого себя необходимо записать один из перечисленных символов, то ему должен предшествовать . Сам (любой подходящий символ) определяется в этом же операторе после ключевого слова СПЕЦСИМВОЛ.
Как видим, возможности даже шире, чет у некоторых СУБД (хотя, например, в SQL Server такое тоже есть).
Операторы "классического" поиска:
Операторы с квадратными скобками позволяет понять, "есть ли такая буква в этом слове". Например:
Также, согласно стандарту, квадратные скобки "понимают" диапазоны букв:
То же касается отрицаний [^..], можно задавать диапазоны символов. Напомню, порядок определяется возрастанием числовых кодов символов в таблице кодировки. При нарушенном порядке [я-а] ничего не найдётся. Сами квадратные скобки тоже можно искать: "[[]" определяет символ "[". Символы "%" и "_" в скобках означают сами себя, а не служебные wildcard.
Сложный шаблон уже даже начинает напоминать регулярное выражение. "ПОДОБНО" реализует упрощённую wildcard-семантику, где "%" заменяет классический "*", а "_" заменяет классический "?".
- Многократный повтор "%" ровно ничего не меняет, "%%%фостарт", "Ин%%%%т" и "%фостарт" равнозначны. А вот "_" требователен к знакоместам, к позициям, и следует быть внимательным к повторам, т.к. "__нфостарт" сработает, а "___нфостарт" нет.
- Все операторы не чувствительны к регистру букв, "Инф%" и "инф%" равнозначны. Интересно, что SQL по умолчанию тоже не учитывает регистр, а PostgreSQL и Оракл - учитывают.
- Все операторы корректно обрабатывают всяческие символы, т.е. коллекцию "Символы", и символ параграфа-конца страницы, и прочий мусор (есть тонкости в национальных частях кодировок, см.ниже). В том числе в конструкциях "[..] и [^..]. Такие могут фигурировать в наполнении таблицы, в условии запроса, и будут правильно найдены (ввести их в строку поиска можно программно или скопипастить откуда-нибудь). "Символ.ПС", несмотря на некоторую синтетичность CR+LF, это один символ.
- Сами себя "%" и "_" правильно находят, на общих основаниях, т.е. "Доход 20% годовых" по "%д 20% г%" будет найден.
- Не следует делать экранирование операторов, как принято в СУБД, т.е. "/%" как раз-таки не сработает. Для экранирования и для уточнения используется оператор "СПЕЦСИМВОЛ" (он же Escape):
Поиск возможен в строковых полях таблиц (в т.ч. неограниченной длины), в переменных неограниченной длины (в т.ч. полях таблиц-параметров и временных таблиц). Но при этом поиск невозможен в полях, полученных функцией "ПредставлениеСсылки", или имеющих, явно или потенциально, значение Null. Если таковое явственно, то даже компиляция, в т.ч. открытие конструктора запроса/СКД, сообщает об ошибке "Неверные параметры", если же нет, то будет ошибка исполнения запроса.
Отбор средствами СКД для "ПОДОБНО" имеет дело с значением колонки таблицы и может использовать индексы, т.е. быть ускорен (в SQL и Oracle так и есть), и создаваемая им нагрузка наблюдаема в профайлере обычным образом. Замечено, что запрос на несколько значений одного и того же поля "спр.Путь ПОДОБНО ""%фостар%"" ИЛИ спр.Путь ПОДОБНО ""%тар_"" и т.д., оптимизируется почти всегда, поэтому опасаться критичного торможения при наложении условия, моделирующего "подобие в списке" для одного поля, не стоит. А вот условия на разные поля, конечно, дадут рост нагрузки и, возможно, мер по оптимизации запроса.
Определённую тонкость представляет кодировка. 1С использует Unicode, и вроде бы защищает нас от специфики СУБД, но, поскольку есть "Внешние источники", запросы и СКД к ним, упомяну о кодировке.
В MS SQL понимание кодировки зависит от SSMS, стыковки сервера скуля с провайдером, правильно выставленного свойства БД Collation (Cyrillic_General_CI_AS). Иногда where f1 like '%нфост%' не работает, а where f1 like N'%нфост%' работает, т.е. стоит перед литеральными юникодными строками писать "N" или делать так: "f1 LIKE Convert(VarChar,N'%нфост%')". Также советуют для юникода применять функции NCHAR(), UNICODE() и т.д, а не CHAR(), ANSI() и т.д. SQL иначе учитывает завершающие пробелы, и с кодировкой ASCII работает по своим старым стандартам, а не по ISO.
В Оракле: есть просто разные операторы:
"обычный" LIKE — применяется для запроса к строковым столбцам с традиционными кодировками;
LIKEC — применяется для столбцов с кодировкой Unicode (в терминологии Oracle — Unicode complete);
LIKE2 — для кодировки UCS2;
LIKE4 — для кодировки UCS4.
Насчёт внешних источников в методическом описании 1С сказано:
"Строковые функции и выражения запроса и СКД преобразуются в запрос к СУБД с использованием управляющих последовательностей ODBC (ODBC Escape Sequences, http://msdn.microsoft.com/en-us/library/windows/desktop/ms715364(v=vs.85).aspx", причём там речь о функции "Подстрока", а "ПОДОБНО" не упомянут вообще нет, но на практике эффект тот же. В других статьях 1С на эту тему никакие ограничения вообще не упомянуты.
И, повторюсь, благодаря 1С в "классических" случаях мы можем не заботиться о кодировке. 1С найдёт даже кракозябр в строке ему подобных.
Часть возможностей "ПОДОБНО" можно смоделировать, используя схожий инструмент - функцию "Подстрока", например:
// вместо
ТекстЗапроса="ВЫБРАТЬ Истина ИЗ Справочник.Сайты КАК спр ГДЕ спр.Наименование ПОДОБНО ""Инфо%""";
// можно
ТекстЗапроса="ВЫБРАТЬ Истина ИЗ Справочник.Сайты КАК спр ГДЕ Подстрока(спр.НаименованиеПодстрока,1,4) = "Инфо";
Но делать это не стоит. И в 1С, и в основных СУБД поиск "Like" обычно быстрее "Substr", т.к. поиск по колонке, могущей иметь (и часто имеющей) индекс быстрее, чем вычисление произвольного выражения в функции. Однако, в "продвинутых" случаях, с помощью т.н. "function-based index" и особо крутой статистики оптимизатор запроса СУБД может-таки заставить вычисление подстроки работать почти так же быстро. Но зачем.
Отличается и поведение. В отличие от "ПОДОБНО", функция "Подстрока" может обработать Null, и её результатом тоже будет Null; но "ПредставлениеСсылки" она тоже не обрабатывает.
Пожалуй, всё. Углубляться в дебри реализации "Like" средствами СУБД не будем.
Несмотря на все недостатки, поиск по текстовому полю до сих пор является одним из самых популярных. Строковые типы данных мы можем встретить повсеместно – наименования, номера счетов, адреса, а также другая информация может храниться в этом формате. В запросах на встроенном языке 1С для удобства разработчиков используется специальный оператор «ПОДОБНО». Это одна из самых используемых команд, поэтому без досконального знания ее синтаксиса и возможностей программисту будет сложно работать.
Использование оператора «ПОДОБНО»
Перед тем, как применять какой-либо оператор на практике, нужно четко понять его предназначение, места применения и синтаксис. Целью использования «ПОДОБНО» в запросе 1С является проверка на удовлетворение условию, представленному в виде шаблона. Возвращаемым значением является логический тип – истина или ложь, демонстрирующий выполняется ли заданное условие. Использоваться оператор ПОДОБНО может в нескольких местах запроса:
- В блоке условий, обозначенном ключевым словом «ГДЕ»;
- В конструкции Выбор Когда Тогда Иначе Конец;
- Непосредственно в полях выборки, как результат сравнения полей.
Синтаксис проверки всегда одинаков и состоит из 3 звеньев. Слева текстовое значение, которое проверяется, затем непосредственно сам оператор «ПОДОБНО», а справа – шаблон, по которому идет проверка. Для быстрого и удобного составления шаблонов существуют специальные символы, облегчающие разработку:
- «%»– последовательность любых символов произвольной длины. Используется для поиска отдельных слов или цифр в строке;
- «_»– любой единичный символ. Предназначен для обозначения наличия одного знака;
- «[…]»– последовательность символов для сравнения со знаком в строке. С помощью подобного шаблона проверяется соответствие любому из перечисленных в скобках знаков. Также можно задать диапазон цифр или букв ([а-г], 7);
- «[^…]»– противоположный предыдущему шаблон. Проверяется отличие указанного в строке символа от перечисленных в скобках.
Чтобы лучше усвоить и понять принципы составления верных шаблонов, рассмотрим некоторые, часто встречающиеся в жизни разработчиков, примеры. Первый, когда нам необходимо выбрать из справочника номенклатуры все позиции, в наименованиях которых встречается слово «РЕЗЕЦ». В этом случае нам необходимо использовать ПОДОБНО в условиях запроса:
Если мы уберем оба символа «%», то запрос покажет номенклатуру, у которой наименование полностью совпадает с указанным в кавычках. Если оставим шаблон «РЕЗЕЦ%» или «%РЕЗЕЦ», то результатом будет список номенклатуры, оканчивающийся или начинающийся, соответственно, на заданную комбинацию символов.
Рис.1 Список номенклатуры с запросом «%РЕЗЕЦ», оканчивающийся на заданную комбинацию символов
Рис.2 Список номенклатуры с запросом «РЕЗЕЦ%», начинающийся на заданную комбинацию символов
Если вам нужно в поиске использовать параметр, то переменная в запросе с параметром ПОДОБНО используется при помощи сложения. Помните, что параметр должен быть строкового типа или вам потребуется перевести ее в строку в запросе. Это достаточно сложная операция и лучше ее исключить заранее.
Функция ПОДОБНО применима во всех версиях платформы, начиная с 8, и ввиду ее применяемости разработчики 1С не захотят ее менять. Конечно, текстовый поиск всегда зависит от точности ввода наименования, но он все равно остается одним из самых распространенных. В связи с этим профессиональным разработчикам 1С необходимо изучить использование ПОДОБНО со всеми его нюансами.
Эти операторы возможно использовать во всех 4 ситуациях, но контекст (окружаемые переменные у них отличаются).
В первых двух случаях условия накладываются на поля выборки, например:
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты КАК СпрКонтрагенты ГДЕ СпрКонтрагенты.ИНН<>""
Обращение идет через имя таблицы (в простых запросах может опускаться, но в сложных может вызывать неоднозначность поля).
Данное условие вызывает ограничение выборки (обычно уменьшает ее)
В конструкторе запроса находятся на вкладке условия:
В произвольном варианте пишется текстом, в обычном выбирается поле, оператор сравнения и значение:
ВЫБОР КОГДА
Этот условный оператор используется для преобразования значения, ограничивает же выборку только когда расположен после служебного слова ГДЕ.
Допустимо несколько подчиненных условий. Может находится в полях выборки, полях условий ГДЕ, группировок, итогов (универсальный оператор для сложных условий)
Например, так выглядит вариант преобразования:
ВЫБРАТЬ
ВЫБОР
КОГДА 1 = 2
ТОГДА "Никогда не будет истиной"
КОГДА 1 = 1
ТОГДА "Всегда будет истиной"
ИНАЧЕ ДАТАВРЕМЯ(1, 1, 1)
КОНЕЦ КАК ПолеПредставленияЗначения
Вариант использования в условии ГДЕ, результат выборки должен возвращать булево значение либо сравниваться с чем-то еще
Пример № 1
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты КАК К ГДЕ ВЫБОР КОГДА К.ИНН = "" ТОГДА ЛОЖЬ ИНАЧЕ ИСТИНА КОНЕЦ
Пример № 2:
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты КАК К ГДЕ ВЫБОР КОГДА К.ИНН = "" ТОГДА "" ИНАЧЕ "Заполнено" КОНЕЦ = "Заполнено"
Параметры виртуальных таблиц
В данном случае оперирование идет с полями таблиц, например ресурсами, измерениями, реквизитами. В этих условиях обращение идет без точки (контекст этого не требует).
ВЫБРАТЬ * ИЗ РегистрСведений.КурсыВалют.СрезПоследних(, Валюта.Код = "RUR")
В конструкторе запроса они находятся вот здесь:
Стоит отметить, что обычно используется ограничение над измерениями, так как иначе условие отнесется к выборке для получения, а не на сам результат (на ресурсы ограничения следует наложить в операторе ГДЕ, чтобы получить ожидаемый результат).
Накладывание условия в запросе на измерения, если нет обращения через точку (как в примере выше), ускоряет выборку: на больших таблицах, это будет визуально заметно; на небольших можно использовать как удобно.
ИМЕЮЩИЕ
Для наложения отбора для результата «группировочных» функции
ВЫБРАТЬ
Контрагенты.ИНН,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагенты.Ссылка) КАК Ссылка
ИЗ
Справочник.Контрагенты КАК Контрагенты
СГРУППИРОВАТЬ ПО
Контрагенты.ИНН
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагенты.Ссылка) > 1 И
Контрагенты.ИНН <> ""
В данную выборку попадут дубли ИНН, когда оно вообще заполнено.
В конструкторе запроса также находится на вкладке условия
В этой конструкции также возможно использование «группировочных» операторов И,ИЛИ, но сравнивать можно с константами запроса, с функцией или значением, входящим в группировку (другие реквизиты справочника в данном примере не будут доступны для сравнения -> тогда следует использовать ГДЕ).
Делать деньги без рекламы может только монетный двор.
— Томас Маколей
Читайте также: