[SOLVED] SQLite query to Lazarus
lazarus / 16-Databases

----------------------------------------------------------------------
DanishMale · 2025-07-11 23:32

Hi,

I've gotten a SQL query directly from DB Browser for SQLite and I need
to convert this to use in a program compiled by Lazarus with use of
TSQLite3Connection

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *,
SUBSTR(`COL2`,1,INSTR(`COL2`,'.')-1) AS SHORT_COL2, COUNT(*) AS Found
FROM `TABLE` WHERELOWER(`COL3`) REGEXP
'(This).(is).(a).(test).(Query)'OR LOWER(`COL3`) REGEXP
'(This).(is).(a).(test).(.*)'OR LOWER(`COL3`) REGEXP
'(This).(is).(a).(.*).(Query)'OR LOWER(`COL3`) REGEXP
'(This).(is).(.*).(test).(Query)'OR LOWER(`COL3`) REGEXP
'(This).(.*).(a).(test).(Query)'OR LOWER(`COL3`) REGEXP
'(This).(is).(a).(.*).(.*)'OR LOWER(`COL3`) REGEXP
'(This).(is).(.*).(.*).(.*)'OR LOWER(`COL3`) REGEXP
'(This).(.*).(.*).(.*).(.*)'  

If I take the above query and try it in my Lazarus program with
TSQLite3Connection I get the error:

Project raised exception class 'ESQLDatabaseError' with message:
TSQLite3Connection : no such function REGEXP

I wonder how to convert this or what ever solution which may be
available to do this, which is to get a result from complete match to
the best possible match from the database

----------------------------------------------------------------------
PierceNg · 2025-07-12 01:22

As per https://sqlite.org/lang_expr.html

QuoteThe 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. If an
application-defined SQL function named "regexp" is added at run-time,
then the "X REGEXP Y" operator will be implemented as a call to
"regexp(Y,X)".
DB Browser has its own regexp() function. To get your query running
as-is in your Pascal program, your options are:

- implement regexp() in your Pascal application as an SQLite custom
function
- rebuild SQLite DLL/so/dylib to incorporate a regexp() function
statically
- if your SQLite DLL/so/dylib allows loading extensions, load a
regexp() implementation DLL/so/dylib

SQLite's source distribution contains a regexp.c. This file is
incorporated into shell.c in the amalgamation, and building the SQLite
CLI from shell.c from source gives you the regexp() function. You can
incorporate regexp.c into your own build of SQLite DLL/so, or build it
as a standalone SQLite extension DLL/so.

There are other REGEXP implementations. E.g.
https://github.com/asg017/sqlite-regex which is implemented in Rust
and has a richer API than regexp.c.

----------------------------------------------------------------------
CharlyTango · 2025-07-12 08:47

Obviously you can extend SQLite
https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-
sqlite-query

I'm not familiar with these cryptic strings regex afficionados
produce. Therefore i asked Grok to reformat the statement.
Im convinced that the answer ist not really working but can give a
hint how to do it

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *, 
     SUBSTR(`COL2`, 1, INSTR(`COL2`, '.') - 1) AS SHORT_COL2,      
 COUNT(*) AS Found FROM `TABLE` WHERE LOWER(`COL3`) LIKE
'this_is_a_test_query'   OR LOWER(`COL3`) LIKE 'this_is_a_test%'   OR
LOWER(`COL3`) LIKE 'this_is_a%query'   OR LOWER(`COL3`) LIKE
'this_is%test_query'   OR LOWER(`COL3`) LIKE 'this%a_test_query'   OR
LOWER(`COL3`) LIKE 'this_is_a%%'   OR LOWER(`COL3`) LIKE 'this_is%%%' 
 OR LOWER(`COL3`) LIKE 'this%%%%'
Perplexity and Claude have another opinion

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *, 
     SUBSTR(`COL2`, 1, INSTR(`COL2`, '.') - 1) AS SHORT_COL2,      
 COUNT(*) AS Found FROM `TABLE`WHERE    LOWER(`COL3`) LIKE
'%this%is%a%test%query%' OR LOWER(`COL3`) LIKE '%this%is%a%test%' OR
LOWER(`COL3`) LIKE '%this%is%a%query%' OR LOWER(`COL3`) LIKE
'%this%is%test%query%' OR LOWER(`COL3`) LIKE '%this%a%test%query%' OR
LOWER(`COL3`) LIKE '%this%is%a%' OR LOWER(`COL3`) LIKE '%this%is%' OR
LOWER(`COL3`) LIKE '%this%';
I'd prefer the second one for a first test ;-)

----------------------------------------------------------------------
Hansvb · 2025-07-12 09:17

Both options with like are strange. Like is usually something:
like ‘ %some text'. Then all strings ending in some text are found.
like 'some text%'. Then all strings that start with some text are
found.
like '%some text%. Then you have all the strings that contain some
text.

----------------------------------------------------------------------
PierceNg · 2025-07-12 10:22

A capable regexp() function will be able to do both case-sensitive and
-insensitive matching, making the lower() calls redundant.

----------------------------------------------------------------------
DanishMale · 2025-07-12 14:37

Thanks to every one who came up with a solution which all brought me
to the answer I needed and choose to go with CharlyTango's suggestion
.....

Again thanks a lot to all  Have a nice day

----------------------------------------------------------------------
Zvoni · 2025-07-14 07:30

From my own Functions:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
USQLiteRegexP;{$mode ObjFPC}{$H+}interface                  uses 
Classes, SysUtils, sqlite3dyn, sqlite3conn, sqltypes;Type 
TSQLiteHelper=class helper for TSQLite3Connection  Public    Procedure
RegisterRegexp;      end; implementationUses regexpr; Var 
regex:TRegexpr; //Own regexp-operator if sqlite-extension is not
available for whatever reasonprocedure RegExp(ctx: psqlite3_context;
n: Integer; args: ppsqlite3_value);cdecl;Var  b:Boolean=False;begin 
regex.ModifierI:=True;  //Customize to suite  regex.ModifierG:=True;
 //Customize to suite  Try   
regex.Expression:=strpas(sqlite3_value_text(args[0]));   
regex.InputString:=strpas(sqlite3_value_text(args[1]));   
b:=regex.Exec;  Except    On E:ERegExpr Do      Begin        b:=False;
             end;  end;  sqlite3_result_int(ctx, b.ToInteger);end;   
                                            { TSQLiteHelper }procedure
TSQLiteHelper.RegisterRegexp;begin 
checkerror(sqlite3_create_function(Self.Handle,                      
       PChar('regexp'),                               2,             
                SQLITE_UTF8 Or SQLITE_DETERMINISTIC,                 
            Nil,                               @RegExp,              
               Nil,                               Nil));end;         
                                           InitializationIf Not
Assigned(regex) Then regex:=TRegexpr.Create;finalizationIf
Assigned(regex) Then regex.Free;end.    
place USQliteRegExP in Uses-clause, specifically AFTER unit
"sqlite3conn"
and after opening the SQLite-Connection execute MyCon.RegisterRegexp

Not really tested to the last possible use-case, nevermind it was more
or less just a proof-of-concept which worked

----------------------------------------------------------------------
PierceNg · 2025-07-15 10:29

Quote from: Zvoni on July 14, 2025, 09:30:48 amFrom my own Functions:
...
Not really tested to the last possible use-case, nevermind it was more
or less just a proof-of-concept which worked
Nice!

Btw, mORMot links SQLite statically, and regexp.c is incorporated.
Thus, when using mORMot, the SQLite regexp() function is available.
Here's a complete mORMot program doing regex seach on the Sakila
database. Obtaining a copy of the database in SQLite format is left as
an exercise for the reader. :-)

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
mormotregex; {$mode delphi}{$H+}{$define FPC_X64MM}{$define
FPCMM_SERVER}{$define FPCMM_REPORTMEMORYLEAKS}{$define
FPCMM_DEBUG} {$I mormot.defines.inc} uses  cthreads, sysutils, 
mormot.db.raw.sqlite3, mormot.db.raw.sqlite3.static,  mormot.db.sql,
mormot.db.sql.sqlite3; procedure sqlite_ormless_regexp;var  connprop:
TSqlDBSQLite3ConnectionProperties;  dbconn: TSqlDBSQLite3Connection; 
sql: String;  stmt: TSqlDBStatement;begin  connprop :=
TSqlDBSQLite3ConnectionProperties.Create('sakila.db', '', '', ''); 
dbconn := TSqlDBSQLite3Connection.Create(connprop);  try   
dbconn.Connect;    sql := 'SELECT first_name, last_name, count(*)
films FROM actor ' +            'JOIN film_actor ' +            'ON
film_actor.actor_id = actor.actor_id ' +            'AND
actor.first_name REGEXP ''ILL'' ' +            'GROUP BY
actor.actor_id, first_name, last_name ' +            'ORDER BY films
DESC ';    stmt := dbconn.NewStatement;    try      stmt.Prepare(sql,
true);      stmt.ExecutePrepared;      while stmt.Step do       
writeln(Format('%s %s made %d films', [stmt.ColumnUtf8(0),
stmt.ColumnUtf8(1), stmt.ColumnInt(2)]));    finally     
stmt.ReleaseRows;      stmt.Free;    end;    dbconn.Disconnect; 
finally    dbconn.Free;    connprop.Free;  end;end; begin 
sqlite_ormless_regexp;end.
When run, the program produces:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i WILL WILSON
made 31 filmsLUCILLE TRACY made 30 filmsMILLA KEITEL made 28
filmsWILLIAM HACKMAN made 27 filmsMILLA PECK made 24 filmsLUCILLE DEE
made 24 films
The REGEXP operator in SQLite is case-sensitive, so searching for
'ill' in the above would yield no row. However, it is also possible to
use function call syntax, and SQLite provides both regexp() and
regexpi(), the latter for case-insensitive matching.

When using regexpi(), the query becomes:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
first_name, last_name, COUNT(*) films FROM actorJOIN film_actorON
film_actor.actor_id = actor.actor_idAND regexpi('ill',
actor.first_name) = 1GROUP BY actor.actor_id, first_name,
last_nameORDER BY films DESC
The regexpi() function is available in the SQLite CLI, but
unfortunately not in current mORMot, because mORMot is using an older
version of regexp.c.