System.Data.SQLite

Would it be possible to add a REGEXP function to the provider?  The SQLIteSpy tool process REGEXP statements, but SQLLite by itself does not.  I think they have done this by adding a user defined functoin, but I don't know how to do this either.

Thanks

Jeff

Can you show me some examples of how you use it in SQLiteSpy?

Robert

 

Here's a quickie example I just made.  Cut and paste it into your code:

[SQLiteFunction(Name = "REGEXP", Arguments = 2, FuncType = FunctionType.Scalar)]
class MyRegEx : SQLiteFunction
{
  public override object Invoke(object[] args)
  {
    return System.Text.RegularExpressions.Regex.IsMatch(Convert.ToString(args[1]), Convert.ToString(args[0]));
  }
}

And an example SQL statement:  SELECT * FROM Foo WHERE Foo.Name REGEXP '$bar'

 

Wow!  It works.  That is an amazingly small amount of code to do such a cool and valuable thing.  Thanks.

Jeff

Hi!

Do you happen to know how we could do the same in VB.Net? Here is what i tried, i get an error on the line <SQLiteFunction(...  for Name, Arguments and FuncType ex: Name "Arguments" is not defined

Thank you!

 

Imports System.Data.SQLite

<SQLiteFunction(Name = "REGEXP", Arguments = 2, FuncType = FunctionType.Scalar)> _
Public Class MyRegEx
    Inherits SQLite.SQLiteFunction

    Public Overrides Function Invoke(ByVal args() As Object) As Object
        Return System.Text.RegularExpressions.Regex.IsMatch(Convert.ToString(args(1)), Convert.ToString(args(0)))
    End Function
End Class

oops ;) i searched a bit more in the forums  and found the answer

so the line should be:

 <SQLiteFunction(Name:="REGEXP", Arguments:=2, FuncType:= FunctionType.Scalar)> _

 

 Is there any way to make it so you can do "SELECT line  FROM table WHERE 'line here' REGEXP line"?

I currently do this with LIKE but would rather have more specific wildcards in the table.

I made a console app and created a regex class in a different namespace (same project binary though), running my code makes it say

 

What did I do wrong?

 Thanks.

An unhandled exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll
Additional information: SQLite error
near "REGEX": syntax error

    static void Main( string[ args )
        {
            SQLiteConnection ObjConnection = new SQLiteConnection( "Data Source=TestFoods.db;" );
            SQLiteCommand ObjCommand = new SQLiteCommand( "SELECT * FROM Foods Where Foods.Name REGEX '$butter' " , ObjConnection );
            ObjCommand.CommandType = CommandType.Text;
            SQLiteDataAdapter ObjDataAdapter = new SQLiteDataAdapter( ObjCommand );
            DataSet dataSet = new DataSet();
            ObjDataAdapter.Fill( dataSet , "Foods" );
            }

 i could really use this.

I've made a Decimal to Binary Convert user defined Function for SQLite , how to add it to Sqlite ?? public string ConvertToBinary(int num) { return Convert.ToString(num, 2).PadLeft(5, '0'); } is there any way i can add this to sqlite?? or is there any existing function in sqlite that does this??

I've added support for implementing this operator in SQLite itself using pcre (which is much faster, and also with caching of the regex) -- the patch files I've attached are for building pcre with vs2008 and against System.Data.SQLite 1.0.65.0. It adds around 150kB to the compiled DLL.

Hope someone finds it useful -- I don't know whether Robert will want to include it in the official release, although I do hope he will :-)