Regular Expressions in the Database Server

Official Content

A regular expression is a powerful way of specifying a pattern for a complex search. Since GeneXus Tilo, when a for each includes a filter with a regular expression; it will be generated using the same expression with the DBMS syntax whenever possible (if the DBMS supports it ).

The developer is able to implement complex match logic in the database. This technique is useful because you move the evaluation of regular expression functions to the database server; the processing logic is closer to the data, thereby it provides a more efficient solution.

The available methods are IsMatch and ReplaceRegEx for character datatypes (Character, Varchar, Longvarchar). For a complete syntax usage check here.

Considerations

The object navigation will detail the filter at database server. For example, suppose the following for each filter

For each
  where AttributeName.IsMatch("(c|h|r)at")
  ...
endfor

You will notice the following constraint:

Constraints:  Attributename.IsMatch(&Pattern)

For those cases where the filter will not be applied on the Database server; the following warning will be added:

/!\ Constraint evaluated in the client. This may lead to poor performance

This means that the filter will be applied in the generated program using standard regular expressions in the associated environment language (C#, Java, Ruby).

Similarly, if you try to use the same attribute on the For Each Order section and the formula cannot be solved on the Database server; the following error is detailed:

/x\ spc0139 Formula 'AttributeFormulaName' cannot be evaluated in server.

Samples

The regular expression "hello|word" matches either the string hello or the string word.

The regular expression "B[an]*s" matches any of the strings Bananas, Baaaaas, Bs, and any other string starting with a B, ending with an s, and containing any number of a or n characters in between.

Limitations

IsMatch method is supported in Oracle, Postgresql, MySql(MySql regular expressions are not 100% compatible with GeneXus regular expressions).
ReplaceRegEx is supported in Oracle, Postgresql.

Considerations

Each DBMS has different support for on regular expression syntax and standard supported.
PostgreSQL supports the POSIX 1003.2 regular expressions format which is stricter than others. For example the following regular expression is not valid:

&variable.IsMatch('ef\z')

See Also

Regular Expressions (RegEx)


Was this page helpful?
What Is This?
Your feedback about this content is important. Let us know what you think.