Synopse Open Source

In order to enable the operator, you should include unit SynSQLite3RegEx.pas to your uses clause, and register the RegExp() SQL function to a given SQLite3 database instance, as such:

uses SynCommons, mORmot, mORMotSQLite3,
  SynSQLite3RegEx;
 ...
Server := TSQLRestServerDB.Create(Model,'test.db3');
try
  CreateRegExFunction(Server.DB.DB);
  with TSQLRecordPeople.CreateAndFillPrepare(Client,
    'FirstName REGEXP ?',['\bFinley\b']) do
  try
    while FillOne do begin
      Check(LastName='Morse');
      Check(IdemPChar(pointer(FirstName),'SAMUEL FINLEY '));
    end;
  finally
    Free;
  end;
finally
  Server.Free;
end;

The above code will execute the following SQL statement (with a prepared parameter for the regular expression itself):

 SELECT * from People WHERE Firstname REGEXP '\bFinley\b';

That is, it will find all objects where TSQLRecordPeople.FirstName will contain the 'Finley' word - in a regular expression, \b defines a word boundary search.

In fact, the REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. Calling CreateRegExFunction() for a given connection will add a SQL function named "regexp()" at run-time, which will be called in order to implement the REGEXP operator.

It will use the statically linked PCRE library as available since Delphi XE, or will rely on the PCRE.pas wrapper unit as published at http://www.regular-expressions.info/download/TPerlRegEx.zip for older versions of Delphi.

This unit will call directly the UTF-8 API of the PCRE library, and maintain a per-connection cache of compiled regular expressions to ensure the best performance possible.

Feedback about this feature request implementation is welcome on our forum, as usual.