1c contains in the request. Similar to query conditions. Using the "Like" Operator

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

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

"%" percent symbol: indicates the presence of any number of arbitrary characters in the 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 character: indicates the presence of any arbitrary character
"[^...]" negation character: denotes the presence of any single character other than those in square brackets

If you need to specify one of the above special characters for comparison, you must use the keyword "SPECIAL CHARACTER"

Features of use with various DBMS

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

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

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

Example: select products whose name starts with the word "Tank"

Example: select products whose name ends with a number

Consider the purpose and use of the conditional operator LIKE in the 1C query language in examples.

Fast passage

purpose

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

Register checking is independent.
Query uses table indexes - not related to full-text search indexes.
It can take a long time for large tables.
Strings of unlimited length should be cast using the SUBSTRING function

Places of use

In operator conditions WHERE
In terms of design CHOICE WHEN<>THEN "" ELSE "" END
In selection fields (for example: Name LIKE & Parameter Similar to String Suitable)

Operator syntax description LIKE

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

The literals (masks) specified below can be used together and separately.

Exact string specification

SELECT first 10
Keys.Name
FROM
Directory.Keys AS Keys
WHERE
Keys.Name LIKE "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 that matches any single 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" followed by any characters

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

Result:

(one or more characters in square brackets)

Each literal that matches any one character is used as an OR.
A range is allowed, for example a-z,0-5, meaning an arbitrary character from the given range

Example

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

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, the exception icon ^ followed by one or more characters)

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

Example

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

Result: all beginning with "8." excluding those

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

As a service character, it is permissible to use at least: #, ~, /, \

Example:

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

Result:

Applicability across platforms

Invalid parameters LIKE<>

A non-string type parameter is passed: for example, the number 1 instead of the string "1"
A field of non-string type is compared with a valid mask (for example, a reference) or, when connected, the value is not checked for NUL

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

Keys.Name LIKE<>&L

Despite all the shortcomings, text field search is still one of the most popular. We can meet string data types everywhere - names, account numbers, addresses, and 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 you put any operator into practice, you need to clearly understand its purpose, places of application and syntax. The purpose of using "LIKE" in the 1C query is to check for satisfaction of the condition presented as a template. The return value is a boolean, true or false, indicating whether the specified condition is met. The LIKE operator can be used in several places in a query:

In the block of conditions indicated by the keyword "WHERE";
In the construction Choice When Then Else End;
Directly in the selection fields, as a result of field comparison.

The syntax of the check 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. For quick and convenient templating, 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;
"_" is any single character. Designed to indicate the presence of one character;
"[...]" is a sequence of characters to compare with a sign in the string. With the help of such a pattern, a match is checked for any of the characters listed in brackets. You can also specify a range of numbers or letters ([a-g], );
“[^…]” is the opposite of the previous pattern. The difference between the character specified in the string and those listed in brackets is checked.

To better assimilate 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 one is when we need to select from the nomenclature reference book all positions in the names of which the word "CUTTER" occurs. 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 LIKE "%CUTTER%"

If we remove both "%" characters, then the query will show the nomenclature, in which the name completely matches the one indicated in quotes. If we leave the template "CUTTER%" or "%CUTTER", then the result will be a list of nomenclature ending or starting, respectively, with a given combination of characters.

Let's analyze a problem that can confuse novice programmers who do not know the query syntax. Let's say you need to find all the nomenclature, in the name of which there is a symbol "%". Especially for cases when you need to search for reserved characters, there is a "special character" operator. As a special character, you can use #, \, /, ~ and other characters, after which any reserved characters will simply denote a character.

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

If you need to use a parameter in the search, then the variable in the query with the LIKE parameter is used by adding. 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 Lookup.Nomenclature AS Nomenclature WHERE Nomenclature.Name LIKE "%" + &name + "%"

The LIKE 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 entering the name, but it still remains one of the most common. In this regard, professional 1C developers need to study the use of LIKE with all its nuances.

In this article, we want to discuss with you all query language functions 1s, as well as query language constructs. What is the difference between a function and a structure? The function is called with brackets and possible parameters in them, and the construction is written without brackets. Undoubtedly all constructions and functions of the 1s query language make the data acquisition process flexible and multifunctional. These functions and constructs apply to query fields, and some also apply to conditions.

Query Language Functions 1s

Since a clear description query language functions 1s is much less common than the description of structures, we decided to start looking at functions. Now let's analyze each one separately, describing its purpose, syntax and usage example, 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 of 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 | DATE DIFFERENCE(DATETIME(2015, 4, 17), DATETIME(2015, 2, 1), DAY) | AS Number of Days";

3. Function VALUE- sets a constant field with a predefined entry from the database, you can also get a null reference of any type.

Syntax: VALUE(<Имя>)

Usage example:

Query.Text = "SELECT //predefined element | VALUE(Currency.Dollar.Catalog.Dollar) AS Dollar, //empty reference | VALUE(Document.IncomingGoodsServices.EmptyReference) AS Receipt, //transfer value | VALUE(Transfer.LegalIndividual. Individual) AS Individual, //predefined account | VALUE(Chart of Accounts.Self-supporting.Materials) AS Account_10" ;

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

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

Usage example:

Request.Text = //if the amount is more than 7500, then there should be a discount of 300 rubles, //therefore, if the condition is triggered, then the function //returns the Amount - 300 //otherwise, the request will simply return the Amount "SELECT | SELECT | WHEN PMIncome.Amount > 7500 | THEN PTReceipts.Amount - 300 | ELSE PTReceipts.Amount | END AS AmountDiscounted |FROM | Document.ReceiptofGoodsServices.Goods AS PTReceipts";

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

Syntax: EXPRESS(FieldName AS TypeName)

Usage example:

Still there is a variant of use of the EXPRESS function in fields of the mixed types where such meet? The simplest example is the "Registrar" for any register. So why would 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? 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 = "CHOOSE | EXPRESS(Nomenclature.Comment AS String(300)) AS Comment, | EXPRESS(Nomenclature.Sum AS Number(15,2)) AS Sum |FROM | Lookup.Nomenclature AS Nomenclature";

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

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

Usage example:

Also note that it is desirable to ALWAYS replace the NULL type with some value, because comparison with NULL always evaluates to FALSE even if you are comparing NULL with NULL. Most often, NULL values are formed as a result of table joins (all types of joins except inner).

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 by the value 0 "SELECT | No.Ref, | ISNULL(GoodsInWarehouseRemains.InStockRemain, 0) HOW Remainder | FROM | Directory. Nomenclature AS Nom | LEFT JOIN Accumulation register.

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

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

Usage example:

Query.Text = "SELECT | REPRESENTATION(Free RemainsRemains.Nomenclature) AS Nomenclature, | REPRESENTATION(FreeRemainsRemains.Warehouse) AS Warehouse, | FreeRemainsRemains.AvailableRemains |FROM | Accumulation Register.FreeRemains.Remains AS FreeRemainsRemains";

Constructions in the query language 1s

Above we discussed with you query language functions 1s, now it's time to consider constructions in query language 1s, they are no less important and useful, let's get started.

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

Usage example:

Query.Text = //if the value type of the registrar is document Receipt, //then the request will return "Incoming Goods", otherwise "Sale of Goods" "SELECT | SELECT | WHEN Remains.Registrar LINK Document.Incoming GoodsServices | THEN ""Incoming" | ELSE "Expense" | END AS Type of Movement | FROM | Accumulation Register. Remains of Goods in Warehouses AS Remains ";

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

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

Usage example:

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

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

Usage example:

Query.Text = // select all sub-accounts of the account "SELECT | Self-supporting. Link AS Account | FROM | Plan of Accounts. Self-supporting AS Self-supporting | WHERE | Self-supporting. Reference IN HIERARCHY VALUE(Schedule of Accounts. Self-supporting. Goods)";

4. Construction LIKE- this function allows us to compare a string against a string pattern.

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

Line template options:

% - a sequence containing any number of arbitrary characters.

One arbitrary character.

[...] - any single character, or a sequence of characters listed inside square brackets. The enumeration can contain ranges, such as a-z, meaning any character within the range, including the ends of the range.

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

Usage example:

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

Syntax: ALLOWED is written after the keyword SELECT

Usage example:

Query.Text = "SELECT ALLOWED | Counterparties.Reference |FROM | Directory.Counterparties AS Counterparties";

6. Construction VARIOUS- allows you to select records in which there are no repeated records.

Syntax: DIFFERENT is written after the keyword SELECT

Usage example:

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

The DIFFERENT construct can also be used with the ALLOWED operator and other operators.

Usage example:

Query.Text = //selects various records to which the reader has rights "SELECT ALLOWED DIFFERENT | Contractors.Name |FROM | Directory.Contractors AS Contractors";

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

Syntax: FIRST<число>

Usage example:

Query.Text = //select the first 4 GTD numbers from the directory "SELECT THE FIRST 4 | GTD Numbers.Reference |FROM | Catalog.GTE Numbers AS GTD Numbers";

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

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

Usage example:

9. Structure ORDER BY- sorts data by a specific field. If the field is a link, then when setting the flag AUTO ORDER will be sorted according to the link representation, if the flag is off, then the 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 certain 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 the data selection condition, similar to the WHERE construction.

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

Usage example:

12. INDEX BY construct- used to index the query field. An indexed query takes longer to complete, but speeds up searches on indexed fields. Can only be used in virtual tables.

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

Usage example:

13. Construction WHERE- allows you to impose a condition on any fields of the selection. Only records that meet the condition will be included in the result.

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

Usage example:

Query.Text = //select all records with CompensationRemainder<>0 and //AmountFor CalculationCompBalance > 100 "CHOOSE | Compensation RPO balances. Counterparty, | Compensation RPO balances.Child, | Compensation RPO balances. Compensation Balance, | Compensation RPO balances.<>0 | And Compensation RPO balances. Amount For CalcComp Residual > 100" ;

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

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

Usage example:

The groupings that were formed during the execution of the request are circled in the figure, the top one refers to the GENERAL section, and the second to the ContractContractorContractType field.

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

After checking, it returns True or False, respectively, it is actively used in conditions.
The following service symbols 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 square brackets
Also, in addition to various characters, ranges can be used, for example a-z(A-z), which means that 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, except for those listed after the negation sign

The remaining characters are used for their intended purpose.
If one of the above service characters must be transmitted as a character, then it must be preceded by<Спецсимвол>. Myself<Спецсимвол>(any suitable character) is defined in the same statement after the SPECIAL CHARACTER keyword.
For example, the pattern “%ABC[abc]\_abc%” 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 character; letters a; letters b; letters in.
Moreover, this sequence can be preceded by an arbitrary set of characters.

Procedure SelectContractContainingInNameText(mText)
//In the request, we will use a template like "%" + mText + "%" Request = New Request; Query.SetParameter("Name", "%" + Text + "%"); Query.Text = "SELECT | Agreements.Reference, | Agreements.Owner |FROM | Directory.AgreementsofCounterparties AS Agreements | |WHERE | Agreements.Name LIKE &Name"; Result = Request.Run(); Selection = Result.Select(); Report("Agreements containing in the name: " + mText + " have the following Counterparties"); While Selection.Next() Loop Report("Account: " + Selection.Owner + "; Contract: " + Selection.Reference EndIf; EndProcedure