Problems with Querries in SQL Lite

[Jeff]

I am hoping there is an SQL expert out there that can help me. I have a plug-in that runs SQL querries against the programme table to look up EPG information. I am using trying to use the regular expression syntax to allow remote control input to do text string look ups. Everything works finr when using Access but the querries don't work when using SQL Lite.

I am using something like this for the match text

WHERE (name LIKE '[8ÙÚÛÜtuvùúûü]%' OR name LIKE '% [8ÙÚÛÜtuvùúûü]%')

I would expect it to match name fields that, for example, have a word that start with t, u, or v. In Access it does; is SQL lite it does not.

Any ideas?

Thanks

Jeff

[David]

I am hoping there is an SQL expert out there that can help me. I have a plug-in that runs SQL querries against the programme table to look up EPG information. I am using trying to use the regular expression syntax to allow remote control input to do text string look ups. Everything works finr when using Access but the querries don't work when using SQL Lite.

I am using something like this for the match text

WHERE (name LIKE '[8ÙÚÛÜtuvùúûü]%' OR name LIKE '% [8ÙÚÛÜtuvùúûü]%')

I would expect it to match name fields that, for example, have a word that start with t, u, or v. In Access it does; is SQL lite it does not.

I do a lot of SQL, but I am not familiar with SQLite. The following web page seems to indicate that regex is not supported in SQLite.
http://refdb.sourceforge.net/manual/sect...SSIONS-SQL

[alibert]

Hi,

I am using something like this for the match text

WHERE (name LIKE '[8ÙÚÛÜtuvùúûü]%' OR name LIKE '% [8ÙÚÛÜtuvùúûü]%')

I would expect it to match name fields that, for example, have a word that start with t, u, or v. In Access it does; is SQL lite it does not.

try the following:

Code:

select * from programme where name regexp '\b[tuv]';

But...
"In SQLite it does; in Access it does not."


-alibert

[Spartan]

I am hoping there is an SQL expert out there that can help me. I have a plug-in that runs SQL querries against the programme table to look up EPG information. I am using trying to use the regular expression syntax to allow remote control input to do text string look ups. Everything works finr when using Access but the querries don't work when using SQL Lite.

I am using something like this for the match text

WHERE (name LIKE '[8ÙÚÛÜtuvùúûü]%' OR name LIKE '% [8ÙÚÛÜtuvùúûü]%')

I would expect it to match name fields that, for example, have a word that start with t, u, or v. In Access it does; is SQL lite it does not.

Any ideas?

Thanks

Jeff

I just installed it on my system and it works great with SQL Server 2005. Your SQL cannot be that far off!

Incidentally, I never knew you could do regex expressions in a where clause. I'll have to tuck that one away in the ol' memory bank.

[Jeff]

Thanks. That works for finding words that start with the given characters, but not for the first word. That is, it will match "Hi There" but not "There Hi"

I tried

select * from programme where name regexp '^[tuv]';

but that doesn't return anything. I assuem the problem is that the ^ is being treated as a not rather than a start of line indicator, but I am not sure how to specify what I want.

Jeff

[alibert]

Hi,

/b also matches the first word of a string. More information on this meta character can be found here, for example.

So, the query posted above should also return programmes whose first character of the title is 't', 'u' or 'v'.

-alibert

[Jeff]

I have been testing the querries with SQLLite Spy and for some reason it didn't seem to be doing that but I will look again.

What if, however, I only want to match a word that is at the start of a line and not match words that appear later?

Jeff

[alibert]

Hi,

What if, however, I only want to match a word that is at the start of a line and not match words that appear later?

start of string:

Code:

select * from programme where name regexp '\A[tuv]';

start of line:

Code:

select * from programme where name regexp '^[tuv]';

By the way those expressions will only match the lowercase characters 't', 'u' and 'v'.

-alibert

[Jeff]

Thanks. I had tried both the \A and the ^ but I forgot that regular expressions are case sensitive (where as SQL LIKE qurerries are not).

Jeff

[Jeff]

Next problem. The querry I am using works fine in SQLiteSpy, but when I put it into my plug-in and execute it in code I get an error.

Here is my querry

Code:

SELECT name, sub_title, description, start_time, end_time, channel_oid, unique_identifier, oid FROM PROGRAMME WHERE name REGEXP '\b[8TUVÙÚÛÜtuvùúûü]' ORDER BY name

Here is the code

Code:

DbCommand myCommand = dbProviderFactory.CreateCommand();
                myCommand.CommandText = mySelectQuery;
                myCommand.Connection = GBPVRDBConnection;
                DbDataReader myReader = myCommand.ExecuteReader();

And here is the error

Code:

System.Data.SQLite.SQLiteException: SQLite error
no such function: REGEXP
   at System.Data.SQLite.SQLite3.Prepare(String strSql, SQLiteStatement previous, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at ShowSearchPlugin.ShowSearchTask.GetShowList(

Any ideas why it thinks there is no REGEXP function?

Jeff