1s 8 is similar in the request. Similar to the query conditions. Using the "like" operator

Let's look at the purpose and use of the conditional operator LIKE(eng. LIKE) in the 1C query language in examples.

Fast passage

Purpose

Check if the string value in the request matches the specified pattern - returns a Boolean value (TRUE or FALSE).

  • The check is case-independent.
  • The query uses table indexes—not related to full-text search indexes.
  • It may take a long time to complete with large tables.
  • Strings of unlimited length should be converted using the SUBSTRING function

Places of use

  • In the terms of the operator WHERE
  • In the design conditions CHOICE WHEN<>THEN "" ELSE "" END
  • In selection fields (for example: Name LIKE &ParameterSimilar to StringSuitable)

Description of the syntax of the LIKE operator

The operator parameter must be a string: it can be specified as a constant, or passed as a request parameter.

The literals (masks) listed below can be used together or separately.

Exact string specification

SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
WHERE
Keys.Name SIMILAR to "1" // Equivalent to Keys.Name ="1"

Result:

% is a literal meaning an arbitrary number of any characters

SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
WHERE
Keys.Name LIKE "%"

Result: any 10 items

_ (underscore): literal matching any one character

Example #1:

SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
WHERE
Keys.Name LIKE "_"

Example #2: starting with any character, followed by “1”, and then any characters

SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
WHERE
Keys.Name SIMILAR to "_1%"

Result:

(one or more characters in square brackets)

  • Each literal that matches any one character is used as an OR.
    It is acceptable to specify a range, for example a-z,0-5, meaning an arbitrary character from the specified range

Example

SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
WHERE
Keys.Name SIMILAR to "[l]%"

Result: 10 starting with "l" or "z"

Example: starting with 5,6,7

SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
WHERE
Keys.Name LIKE "%"

Result:

[^] (in square brackets there is an escape sign ^ followed by one or more characters)

Equivalent to any character (_) except those specified ()

Example

SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
WHERE
Keys.Name SIMILAR to "8.[^012]%"//do not include 8.0,8.1,8.2

Result: all starting with "8." excluding those indicated

SPECIAL CHARACTER - command for specifying the characters registered above in the request

As a service symbol, it is acceptable to use at least: #,~,/,\

Example:

SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
WHERE
Keys.Name LIKE "#_" SPECIAL CHARACTER "#"

Result:

Applicability in platforms

Incorrect parameters LIKE<>

  • The parameter is not passed string type: for example the number 1 instead of the string "1"
  • A non-string type field is compared with a valid mask (for example, a link) or when connecting, the value is not checked for IsNUL

Pay attention to the error text where the question is displayed:

Keys.Name SIMILAR<>&L

Sometimes a situation arises when in 1C 8.3 or 8.2 you need to make a selection, for example, from a directory of all elements that have the word “glaze” in their name. Or, from the directory, select all contractors whose surnames contain the word “Ivan”. In general, check some string value.

For this purpose, there is an operator in 1C queries 8.3 and 8.2 - “Similar”. It is used, respectively, in the following conditions:

Get 267 video lessons on 1C for free:

How to use templates in 1C queries?

To generate a selection condition, you need to pass a certain template as a parameter. To create a template, there are so-called service symbols.

For example, the "%" character allows any sequence of arbitrary characters:

There are other special characters:

  • % (percentage) - allows any sequence of arbitrary characters;
  • _ (underscore) - any single character;
  • […] – one arbitrary character from those listed inside the brackets. In addition to listing characters, you can use ranges. Example: a-o;
  • [^...] – the same as the previous one, but in reverse. The "^" sign means negation.

Operator LIKE allows you to compare string type data in a query that is located to the left of the operator with string type data that is located to the right of the operator. The result of the comparison evaluates to True or False, so the comparison can be applied as a condition.

For the operator LIKE There are special service characters that are not perceived as a string:

  • "%" percent symbol: indicates the presence of any number of arbitrary characters in a string
  • "[...]" one or more characters in square brackets: indicates the presence of any (single) of the listed characters. Also, a range of characters can be specified (for example)
  • "_" underscore: indicates the presence of any arbitrary character
  • "[^...]" negation character: denotes the presence of any single character other than those specified in square brackets
If you need to specify one of the above special characters for comparison, you must use the keyword "SPECIAL SYMBOL"

Features of use with various DBMSs

IBM DB2"Only a parameter can be placed to the right of the SIMILAR operator. The only wildcard characters are "_" (underscore meaning any character) and "%" (percentage meaning a sequence of any characters).
In case of using a DBMS " PostgreSQL" or " Oracle Database"special characters "square brackets [...]" are accepted only if they are specified in the text in the request, and are NOT passed as a parameter to the request.

Thus, in the file database, special characters will always be perceived the same way, and differently depending on the DBMS used in the client-server version.

Example: select products containing the symbol "%" in the name

SELECT | Ref.Link |FROM | Directory. Nomenclature HOW Ref | WHERE | Ref.Name LIKE "%\%" SPECIAL CHARACTER "\"

Example: select products whose names begin with the word "Tank"

SELECT | Ref.Link |FROM | Directory. Nomenclature HOW Ref | WHERE | Ref.Name SIMILAR to "Bak%"

Example: select products whose names end with a number

SELECT | Ref.Link |FROM | Directory. Nomenclature HOW Ref | WHERE | Ref.Name SIMILAR to "%"

The SIMILAR operator in a query checks string values ​​from tables for similarity to a pattern.
It is used as follows: the string to be checked is placed to the left of this operator, and the pattern is placed to the right.

After checking, it returns True or False; accordingly, it is actively used in conditions.
The following service characters are used to create a template:

  • % (percentage) - a sequence containing any number of arbitrary characters
  • _ (underscore) - one arbitrary character
  • […] (one or more characters in square brackets) - any single character listed inside the square brackets
    Also except various characters you can use ranges, for example a-z(A-z), which means there is an arbitrary character included in the range, including the ends of the range.
  • [^...] (in square brackets a negation sign followed by one or more characters) - any single character other than those listed after the negation sign

The remaining symbols are used for their intended purpose.
If it is necessary to transmit one of the above service characters as a symbol, then it must be preceded by<Спецсимвол>. Myself<Спецсимвол>(any suitable symbol) is defined in the same statement after keyword SPECIAL SYMBOL.
For example, the pattern “%ABV[abvg]\_abv%” SPECIAL CHARACTER “\” means a substring consisting of a sequence of characters:
letters A; letters B; letters B; one digit; one of the letters a, b, c or d; underscore; letters a; letters b; letters v.
Moreover, this sequence can be preceded by an arbitrary set of characters.

Procedure Select AgreementContainingInNameText(mText)
//In the request we will use a template like "%" + mText + "%" Request = New Request; Query.SetParameter("Name", "%" + Text + "%"); Request.Text = "SELECT | Agreements. Link, | Agreements. Owner | FROM | Directory. Contracts of Counterparties AS Agreements | | WHERE | Agreements. Name SIMILAR & Name"; Result = Query.Run(); Selection = Result.Select(); Report("Agreements containing in the name: " + mText + " have the following Counterparties"); While Selection.Next() Cycle Report("Counterparty: " + Selection.Owner + "; Agreement: " + Selection. Link EndIf; EndProcedure

  43
 NULL – missing values. Not to be confused with zero value! NULL is not a number, does not equal a space, an empty reference, or Undefined. NULL is a type-forming value, i.e. there is a type NULL and a single value of this type. NULL...   26
 To generate and execute queries to database tables in the 1C platform, a special object of the Query programming language is used. This object is created by calling the New Request construct. Convenient request...   18
 The article provides useful techniques when working with 1C v.8.2 queries, as well as information that is not so well known about the query language. I am not trying to give a complete description of the query language, but want to dwell only on...   12
 I was faced with the task of selecting all payment documents and grouping them by document type! Having looked through all the housing and communal services and the Internet, I realized that simple way get Document type is not in the request:(I had to...