REGEXP for sqlite?

[camille]

Hi,

is there any way to add that function to sqlite in PB?

https://www.sqlite.org/lang_expr.html#regexp

Regards,
Camille

[infratec]

You can do this already.

As writtten in the SQLite help, you have to create an own function named regexp()

This can be done like here
viewtopic.php?p=532043#p532043

[infratec]

Working example

Code: Select all

ImportC ""
  sqlite3_create_function.i(DatabaseID, zFunctionName.p-utf8, nArg.i, eTextRep.i, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_aggregate_context(*sqlite3_context, nBytes.i)
  sqlite3_result_int(*sqlite3_context, int.i)
  sqlite3_result_double(*sqlite3_context, dbl.d)
  sqlite3_result_text(*sqlite3_context, *returnvalue, nBytes.i, *nParam)
  sqlite3_value_numeric_type.i(*sqlite3_value)
  sqlite3_value_double.d(*sqlite3_value)
  sqlite3_value_int.i(*sqlite3_value)
  sqlite3_value_text16.i(*sqlite3_value)
  
  sqlite3_value_type.i(*argv)
  
  ;Neue Funktionen
  sqlite3_backup_init(pDest, zDestName.p-ascii, pSource, zSourceName.p-ascii)
  sqlite3_backup_step(sqlite3_backup, nPage)
  sqlite3_backup_finish(sqlite3_backup)
  sqlite3_errcode(db)
EndImport


#SQLITE_UTF8 = 1  ; IMP: R-37514-35566
#SQLITE_UTF16LE = 2 ; IMP: R-03371-37637
#SQLITE_UTF16BE = 3 ; IMP: R-51971-34154
#SQLITE_UTF16 = 4   ; Use native byte order
#SQLITE_ANY = 5     ; Deprecated
#SQLITE_UTF16_ALIGNED = 8 ; sqlite3_create_collation only


#SQLITE_INTEGER = 1
#SQLITE_FLOAT = 2
#SQLITE_TEXT = 3
#SQLITE_BLOB = 4
#SQLITE_NULL = 5

#SQLITE_STATIC = 0
#SQLITE_TRANSIENT = -1



Structure IntegerArrayStructure
  ptr.i[0]
EndStructure


ProcedureC RegExP(*context, argc.i, *argv.IntegerArrayStructure)
  
  Protected RegEx$, Value$, RegEx.i, Result.i
  
  
  RegEx$ = PeekS(sqlite3_value_text16(*argv\ptr[0]))
  Value$ = PeekS(sqlite3_value_text16(*argv\ptr[1]))
  
  RegEx = CreateRegularExpression(#PB_Any, RegEx$)
  If RegEx
    If MatchRegularExpression(RegEx, Value$)
      Result = #True
    EndIf
    FreeRegularExpression(RegEx)
  EndIf
  
  sqlite3_result_int(*context, Result)
  
EndProcedure

Procedure RegisterSQLiteFunctions(dataBase.i)
  
  Protected dataBaseID.i
  
  
  dataBaseID = DatabaseID(dataBase)
  sqlite3_create_function(dataBaseID, "regexp",  2, #SQLITE_UTF8, #Null, @RegExP(), #Null, #Null)
  
EndProcedure


UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite)
If DB
  RegisterSQLiteFunctions(DB)
  
  DatabaseUpdate(DB, "CREATE TABLE myTable (description text)")
  DatabaseUpdate(DB, "INSERT INTO myTable VALUES ('PureBasic')")
  DatabaseUpdate(DB, "INSERT INTO myTable VALUES ('SpiderBasic')")
  
  If DatabaseQuery(DB, "SELECT * FROM myTable WHERE description REGEXP '.*re.*'")
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0)
    Wend
    FinishDatabaseQuery(DB)
  Else
    Debug DatabaseError()
  EndIf
  
  CloseDatabase(DB)
EndIf

[Captn. Jinguji]

Thanks a lot, infratec,
saved me quite some time not having to work this out myself.

[camille]

Thanks a lot, @infratec!