regexp

[tumpliner]

Is regexp available for use with this product? If so, how would I go about implementing it?
I have dotConnect for SQLite and tried using it. No luck.

[Pinturiccio]

The regular expressions are available in dotConnect for SQLite. In SQLite documentation you can find the following: "No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message". For more information, please refer to http://www.sqlite.org/lang_expr.html#regexp

So we need to add the "regexp" function in our code. The following example will show how implement the regexp function.

The DDL/DML scrips:

Code: Select all

CREATE TABLE DEPT(
  DEPTNO INTEGER PRIMARY KEY,
  DNAME VARCHAR(14),
  LOC VARCHAR(13)
);

INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (1, 'Butcher', 'Vegas');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (2, 'Sailor', 'New York');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (3, 'Geologist', 'Last York');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (4, 'Teacher', 'New Yoork');

The application code:

Code: Select all

    public class MyFunction : SQLiteScalarFunction
    {

        public MyFunction()
            : base("REGEXP", 2)
        {
        }

        protected override object Execute(object[] args, SQLiteConnection connection)
        {
            return System.Text.RegularExpressions.Regex.IsMatch(Convert.ToString(args[1]), Convert.ToString(args

[0]));
        }
    } 
    
    class Program
    {
        static void Main(string[] args)
        {
            SQLiteConnection conn = new SQLiteConnection(@"Data Source=D:\TestApplication\database.db");
            conn.Open();
            MyFunction function = new MyFunction();
            conn.RegisterFunction(function);
            SQLiteCommand comm = new SQLiteCommand("SELECT * FROM dept WHERE loc REGEXP 'New|York'", conn);
            SQLiteDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                    Console.Write(reader.GetValue(i) + "\t");
                Console.WriteLine();
            }
        }
    }