vendredi 20 mars 2015

Unable to use REGEXP operator in System.Data.SQLite

Suppose that I have the following table and data inside it:



CREATE TABLE urls (url TEXT);
INSERT INTO urls VALUES('http://ift.tt/1FcOojE');
INSERT INTO urls VALUES('http://ift.tt/1BWkvjP');
INSERT INTO urls VALUES('http://ift.tt/1BWkvjR');
INSERT INTO urls VALUES('http://ift.tt/1FcOlUT');


I need to select all records where URL contains non-empty query parameter (in this case it's all but the first record).


I'm using SQLite, so I decided to use REGEXP operator like this:



string sql = @"SELECT * FROM urls WHERE url REGEXP '\/(?:www\.)?google.+?q=([^&]+)(?:&|$)'";
var command = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("Url: " + reader["url"]);
}


but it gives me the following error:



An unhandled exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll


Additional information: SQL logic error or missing database


no such function: REGEXP



Is there any way to fix it? If no, what can I do instead?


Thanks in advance.


Aucun commentaire:

Enregistrer un commentaire