Specifics of using LIKE operator in queries – 1Ci Support

Scope: managed applications, mobile applications, and ordinary applications.

1. When using LIKE in query texts, use only constant string literals or query parameters. Do not generate a template string using calculation or use string concatenation with the query language. For example:

Valid:

Attribute LIKE "123%"

Invalid:

Attribute LIKE "123" + "%"
Attribute LIKE Table.Template

2. Queries where control characters of the LIKE operator template are in query fields or expressions being calculated are interpreted differently in various database management systems. Queries that are successfully executed, for example, when you use a file database can return incorrect results in client/server mode. Such expressions must be restated.

For example, instead of:

Query = NewQuery("
    |SELECT
    |    Goods.Ref
    |FROM
    |    Catalog.Goods AS Goods
    |WHERE
    |    Goods.OriginCountry.Description LIKE &CountryNameTemplate + "_"
    |");

Query.SetParameter("CountryNameTemplate", "NA");

Use:

Query = New Query("
    |SELECT
    |    Goods.Ref
    |FROM
    |    Catalog.Goods AS Goods
    |WHERE
    |    Goods.OriginCountry.Description LIKE &CountryNameTemplate
    |");

Query.SetParameter("CountryNameTemplate", "NA_");

This requirement is based on some specifics of migrating applications to various database management systems.

See also: