On 01/29/2008 11:16 PM, Bharath Booshan L wrote:
How can I instruct GLOB function to perform case-insensitive search similar to LIKE. Can I?
Bharath -- A lot depends on the character set you choose to use. If you use US ASCII, there is a collation (COLLATE NOCASE) that could handle this for you. See: http://www.sqlite.org/lang_expr.html There is also a discussion of the REGEXP Function on that page and why your app threw an error when you tried to invoke a REGEXP filter in your query. Back to your original table (call it t1), one way to do case insensitive filters would be to add COLLATE NOCASE: CREATE TABLE t1 ( ID INTEGER, PathName VARCHAR(255) COLLATE NOCASE, -- contains `dirname MovieFile` FileName VARCHAR(255) COLLATE NOCASE -- contains `basename MovieFile` ) ; In this case, COLLATE NOCASE makes both PathName and FileName filters case insensitive for the US ASCII character set. I am not sure what your application is ultimately going to do. Adding INDEXes to a table is always a balancing act between performance on INSERTs versus SELECTs. You'll have to decide for yourself -- a lot depends on the number of records in the table -- tens of records won't need an index, hundreds of records might work better with INDEXes, thousands probably will most likely run better with INDEXes). To answer your question from yesterday about using indexes on that table, if you add the following two INDEXes (note that the table name is t1), you could query via INDEX on either PathName or FileName: create index t1PathName on t1( PathName ) ; create index t1FileName on t1( FileName ) ; The COLLATE NOCASE expressions in the CREATE TABLE statement will allow case insensitive searches. For example, to find all the movies in a PathName (directory): SELECT ID as "ID", PathName || '/' || FileName as "FilePath" FROM t1 WHERE PathName LIKE '/volumes/backup/mymov%' ORDER BY FileName ; The query should use the t1PathName INDEX because the % wildcard is at the end of the constant '/volumes/backup/mymov%' To find all the movies starting with 'mymov' (case insensitive): SELECT ID as "ID", PathName || '/' || FileName as "FilePath" FROM t1 WHERE FileName GLOB 'mymov*.???' ORDER BY "FilePath" ; That query should use the t1FileName INDEX because the '*.???' wildcard is at the end of the constant 'mymov*.???' HTH -- have fun ! -- kjh ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------