Similar to the query conditions. Similar in the request conditions Operator similar in the 1c request

Despite all the shortcomings, text field search is still one of the most popular. We can find string data types everywhere - names, account numbers, addresses, as well as other information can be stored in this format. In queries in the built-in 1C language, for the convenience of developers, a special operator “LIKE” is used. This is one of the most used commands, so without a thorough knowledge of its syntax and capabilities, it will be difficult for a programmer to work.

Using the LIKE operator

Before using any operator in practice, you need to clearly understand its purpose, places of application and syntax. The purpose of using “LIKE” in a 1C request is to check for satisfaction of the condition presented as a template. The return value is a Boolean type - true or false - indicating whether the specified condition is true. The LIKE operator can be used in several places in a query:

In the block of conditions, indicated by the keyword “WHERE”;
In the design of Choice When Then Otherwise End;
Directly in the selection fields, as a result of field comparison.

The verification syntax is always the same and consists of 3 links. On the left is the text value that is being checked, then the “LIKE” operator itself, and on the right is the template that is being checked. To quickly and easily create templates, there are special symbols that make development easier:

“%” is a sequence of any characters of arbitrary length. Used to search for individual words or numbers in a string;
“_” – any single character. Intended to indicate the presence of a single character;
“[...]” is a sequence of characters to compare with a character in a string. With the help of such a pattern, a match to any of the characters listed in brackets is checked. You can also specify a range of numbers or letters ([a-g], );
“[^...]” is the opposite pattern to the previous one. The difference between the character specified in the line and those listed in brackets is checked.

To better understand and understand the principles of creating correct templates, let's look at some examples that are often encountered in the life of developers. The first is when we need to select from the nomenclature directory all items in the names of which the word “CUTTER” appears. In this case, we need to use LIKE in the query conditions:

SELECT Nomenclature.Name AS Name FROM Directory.Nomenclature AS Nomenclature WHERE Nomenclature.Name SIMILAR to "% CUTTER%"

If we remove both “%” symbols, the query will show a product whose name completely matches that specified in quotation marks. If we leave the template “CUTTER%” or “% CUTTER”, then the result will be a list of items ending or beginning, respectively, with a given combination of characters.

Let's look at a problem that can confuse novice programmers who do not know query syntax. Let’s say you need to find all items that have the “%” symbol in their names. Especially for cases when you need to search for reserved characters, there is a “special character” operator. #,\,/,~ and other characters can be used as a special character, after which any reserved characters will simply denote a sign.

SELECT Nomenclature.Name AS Name FROM Directory.Nomenclature AS Nomenclature WHERE Nomenclature.Name SIMILAR to "%#%" SPECIAL CHARACTER "#"

If you need to use a parameter in a search, then the variable in the query with the SIMILAR parameter is used using addition. Remember that the parameter must be a string type or you will need to convert it to a string in the request. This is a rather complicated operation and it is better to exclude it in advance.

SELECT Nomenclature.Name AS Name FROM Directory.Nomenclature AS Nomenclature WHERE Nomenclature.Name SIMILAR to "%" + &name + "%"

The SIMILAR function is applicable in all versions of the platform, starting from 8, and due to its applicability, 1C developers will not want to change it. Of course, text search always depends on the accuracy of the name entered, but it still remains one of the most common. In this regard, professional 1C developers need to study the use of SIMILAR with all its nuances.

43
NULL – missing values. 26
Not to be confused with zero value! NULL is not a number, does not equal a space, an empty reference, or Undefined. 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 there is no easy way to get the Document Type in the request: (I had to... 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

Exact string specification
SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
Example #1:

Keys.Name LIKE "_" Example #2:

Exact string specification
SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
starting with any character, followed by “1”, and then any characters

Keys.Name SIMILAR to "1" // Equivalent to Keys.Name ="1"

Keys.Name SIMILAR to "_1%"

(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

Exact string specification
SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
Example

Keys.Name LIKE "%" Keys.Name SIMILAR to "[l]%"

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

Exact string specification
SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
WHERE

Keys.Name SIMILAR to "1" // Equivalent to Keys.Name ="1"

starting with 5,6,7

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

It is acceptable to specify a range, for example a-z,0-5, meaning an arbitrary character from the specified range

Exact string specification
SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
Equivalent to any character (_) except those specified ()

Keys.Name LIKE "%": 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: #,~,/,\

: 10 starting with "l" or "z"

Exact string specification
SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
Keys.Name LIKE "#_" SPECIAL CHARACTER "#"

Keys.Name SIMILAR to "1" // Equivalent to Keys.Name ="1"

Applicability in platforms

Incorrect parameters LIKE<>

A non-string type parameter is passed: 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

In this article we want to discuss everything with you 1C query language functions, and query language constructs. What is the difference between function and design? The function is called with parentheses and possible parameters in them, and the construct is written without parentheses. Undoubtedly all structures and functions of the 1C query language make the data acquisition process flexible and multifunctional. These functions and constructs apply to request fields, and some also apply to conditions.

1C Query Language Functions

Because a clear description 1c query language functions is much less common than descriptions of structures, we decided to start looking at functions. Now let's look at each one separately, describing its purpose, syntax and example of use, so:

1. Function DATE TIME- this function creates a constant field with the "Date" type.

Syntax: DATE TIME(<Год>,<Месяц>,<День>,<Час>,<Минута>,<Секунда>)

Usage example:

2. DATE DIFFERENCE function- returns the difference between two dates in one of the dimensions (year, month, day, hour, minute, second). The measurement is passed as a parameter.

Syntax: DIFFERENCEDATE(<Дата1>, <Дата2>, <Тип>)

Usage example:

Query.Text = "SELECT | DIFFERENCEDATE(DATETIME(2015, 4, 17), DATETIME(2015, 2, 1), DAY) | AS Number of Days";

3. Function VALUE- sets a constant field with a predefined record from the database; you can also get an empty link of any type.

Syntax: VALUE(<Имя>)

Usage example:

Request.Text = "SELECT //predefined element | VALUE(Directory.Currencies.Dollar) AS Dollar, //empty link | VALUE(Document.Receipt of Goods and Services.EmptyLink) AS Receipt, //transfer value | VALUE(Transfer. Legal Individual. Individual) AS Individual, //predefined account VALUE(Chart of Accounts. Self-Accounting.Materials) AS Account_10" ;

4. SELECT function- we have before us an analogue of the IF construction, which is used in the code, only this one is used in 1C queries.

Syntax: CHOICE WHEN<Выражение>THEN<Выражение>OTHERWISE<Выражение>END

Usage example:

Request.Text = //if the amount is more than 7500, then there should be a discount of 300 rubles, //so if the condition is triggered then the function //returns Amount - 300 //otherwise the request will simply return Amount "SELECT | SELECT | WHEN TCReceipts.Amount > 7500 | THEN TCReceipts.Amount - 300 | ELSE TCReceipts.Amount | END AS AmountWithDiscount | FROM |

5. EXPRESS function- allows you to express a constant field with a specific type.

Syntax: EXPRESS(FieldName AS TypeName)

Usage example:

Is there another option for using the EXPRESS function in fields of mixed types, where do they occur? The simplest example is the “Registrar” for any register. So why might we need to qualify the type in the registrar? Let's consider the situation when we select the "Number" field from the registrar, from which table will the number be selected? The correct answer of all! Therefore, in order for our query to work quickly, we must specify an explicit type using the EXPRESS function

Usage example:

Query.Text = "SELECT | EXPRESS(Nomenclature.Comment AS Line(300)) AS Comment, | EXPRESS(Nomenclature.Sum AS Number(15,2)) AS Sum |FROM | Directory.Nomenclature AS Nomenclature";

6. ISNULL function(alternative spelling ISNULL) - if the field is of type NULL, then it is replaced with the second parameter of the function.

Syntax: ISNULL(<Поле>, <ПодставляемоеЗначение>)

Usage example:

Also note that it is advisable to ALWAYS replace the NULL type with some value, because comparison with type NULL always returns FALSE even if you compare NULL with NULL. Most often, NULL values are formed as a result of joining tables (all types of joins except internal ones).

Query.Text = //Select the entire item and its balances //if there is no balance in some item, then there will be a field //NULL which will be replaced with the value 0 "SELECT | No. Link, | ISNULL(ProductsInStockRemains.InStockRemaining, 0) HOW Remaining | FROM | Directory.Nomenclature AS No. | LEFT CONNECTION RegisterAccumulations.GoodsInWarehouses.Remainings AS GoodsInWarehousesRemainings | PO (GoodsInWarehousesRemainings.Nomenclature = No.Link)";

7. REPRESENTATION function- allows you to get a representation of the request field.

Syntax: PERFORMANCE(<НаименованиеПоля>)

Usage example:

Query.Text = "SELECT | REPRESENTATION(FreeRemainingRemains.Nomenclature) AS Nomenclature, | REPRESENTATION(FreeRemainingRemaining.Warehouse) AS Warehouse, | FreeRemainingRemaining.InStockRemaining |FROM |Accumulation Register.FreeRemaining.Remaining AS FreeRemainingRemaining";

Constructs in the 1C query language

We discussed with you above 1C query language functions, now it's time to consider constructs in the 1C query language, they are no less important and useful, let’s get started.

1. Construction LINK- is a logical operator for checking a reference type. Most often encountered when checking a field of a complex type against a specific type. Syntax: LINK<Имя таблицы>

Usage example:

Request.Text = //if the recorder value type is document Receipt, //then the query will return "Receipt of goods", otherwise "Sales of goods" "SELECT | SELECT | WHEN Remaining.Registrar LINK Document.Receipt of GoodsServices | THEN ""Receipt"" | ELSE ""Consumption"" | END AS Type of Movement | FROM | Register of Accumulation. Remaining Products in Warehouses AS Remaining" ;

2. Design BETWEEN- this operator checks whether the value is within the specified range.

Syntax: BETWEEN<Выражение>AND<Выражение>

Usage example:

3. Construction B and B HIERARCHY- check whether the value is in the transferred list (arrays, tables of values, etc. can be transferred as a list). The IN HIERARCHY operator allows you to view the hierarchy (an example of using the Chart of Accounts).

Syntax: IN(<СписокЗначений>), IN HIERARCHY(<СписокЗначений>)

Usage example:

4. Design SIMILAR- This function allows us to compare a string with a string pattern.

Syntax: LIKE "<ТекстШаблона>"

Row pattern options:

% - a sequence containing any number of arbitrary characters.

One arbitrary character.

[...] - any single character or sequence of characters listed inside square brackets. The enumeration can specify ranges, for example a-z, meaning an arbitrary character included in the range, including the ends of the range.

[^...] - any single character or sequence of characters listed inside square brackets except those listed after the negation sign.

Usage example:

5. Design ALLOWED- this operator allows you to select only those records from the database for which the caller has read permission. These rights are configured at the record level (RLS).

Syntax: ALLOWED is written after the keyword SELECT

Usage example:

Request.Text = "SELECT ALLOWED | Counterparties. Link | FROM | Directory. Counterparties AS Counterparties";

6. Design VARIOUS- allows you to select records in which there are no duplicate records.

Syntax: VARIOUS is written after the keyword SELECT

Usage example:

Request.Text = //selects records to which the reader has rights "SELECT VARIOUS | Counterparties.Name |FROM | Directory. Counterparties AS Counterparties" ;

Also, the VARIOUS construction can be used with the ALLOWED operator and other operators.

Usage example:

Request.Text = //selects various records to which the reader has rights "SELECT ALLOWED VARIOUS | Counterparties.Name |FROM | Directory. Counterparties AS Counterparties";

7. Design FIRST- selects the number of records specified in the parameter from the query result.

Syntax: FIRST<число>

Usage example:

Request.Text = //select the first 4 CCD numbers from the directory "SELECT FIRST 4 | CCD Numbers. Link | FROM | Directory. CCD Numbers AS CCD Numbers";

8. Design FOR CHANGE- allows you to lock a table, works only in transactions (relevant only for automatic locks).

Syntax: FOR CHANGE<НаименованиеТаблицы>

Usage example:

9. Design ORDER BY- organizes data by a specific field. If the field is a link, then when setting the flag AUTO ORDER Sorting will occur by link representation; if the flag is turned off, then links are sorted by the seniority of the link address in memory.

Syntax: SORT BY<НаименованиеПоля>AUTO ORDER

Usage example:

10. Design GROUP BY- used to group query strings by specific fields. Numeric fields must be used with any aggregate function.

Syntax: GROUP BY<НаименованиеПоля1>, .... , <НаименованиеПоляN>

Usage example:

11. Design HAVING- allows you to apply an aggregate function to a data selection condition, similar to the WHERE construction.

Syntax: HAVING<агрегатная функция с условием>

Usage example:

12. Construction INDEX BY- used for indexing the query field. A query with indexing takes longer to complete, but speeds up searching through indexed fields. Can only be used in virtual tables.

Syntax: INDEX BY<Поле1, ... , ПолеN>

Usage example:

13. Design WHERE- allows you to impose a condition on any selection fields. The result will include only records that satisfy the condition.

Syntax: WHERE<Условие1 ОператорЛогСоединения УсловиеN>

Usage example:

Query.Text = //all records with CompensationRemaining are selected<>0 and //AmountForCalcCompRemaining > 100 "SELECT | CompensationRPORemains.Counterparty, |CompensationRPORemains.Child, | CompensationRPORemains.CompensationRemaining, | CompensationRPORemains.AmountForCalcCompRemains |Place DataTz |FROM |Accumulation Register.CompensationRP.Remains AS CompensationRPRemains |WHERE |CompensationRPORemaining.CompensationRemaining<>0 | And CompensationRPORemains.AmountForCalcCompRemaining> 100" ;

14. Design RESULTS... GENERAL- used to calculate totals; the design specifies the fields by which totals will be calculated and aggregate functions applied to the total fields. When using totals for each field following the TOTAL construction, data is grouped. There is an optional GENERAL construct; its use also provides additional grouping. You will see an example of the request result below.

Syntax: RESULTS<АгрегатнаяФункция1, ... , АгрегатнаяФункцияN>BY<ОБЩИЕ> <Поле1, ... , ПолеN>

Usage example:

The figure outlines the groupings that were formed during the execution of the request, the top one refers to the GENERAL section, and the second to the Counterparty AgreementAgreement Type field.

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.

It might be useful to read: