Unable to use REGEXP operator in System.Data.SQLite

Question

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

CREATE TABLE urls (url TEXT);
INSERT INTO urls VALUES('https://www.google.ru/?gws_rd=ssl#newwindow=1&q=&safe=off');
INSERT INTO urls VALUES('https://www.google.ru/?gws_rd=ssl#newwindow=1&safe=off&q=1');
INSERT INTO urls VALUES('https://www.google.ru/?gws_rd=ssl#newwindow=1&q=2&safe=off');
INSERT INTO urls VALUES('https://www.google.ru/?gws_rd=ssl#newwindow=1&q=23&safe=off');

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.

Gordon Linoff · Accepted Answer · 2015-03-20 11:54:39Z

3

SQLite doesn't have a built-in regexp() function, as explained in the documentation:

The 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)".

I admit this it is unusual for a database to support syntax but not the underlying function. However, it should be easy enough to supply your own function.

answered Mar 20, 2015 at 11:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FrozenHeart Over a year ago

How can I supply my own regexp function? Is there any implementation?

Lorenz Meyer · Accepted Answer · 2015-03-20 11:58:28Z

0

The regexp comparison operator is not part of the SQL standard. It is an extension specific to mysql.

Probably you can just use like. This should do the same :

url LIKE '%google.%q=%'

answered Mar 20, 2015 at 11:58

Lorenz Meyer

20.1k23 gold badges83 silver badges130 bronze badges

Collectives™ on Stack Overflow

Unable to use REGEXP operator in System.Data.SQLite

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related