Re: [sqlite] How to specify regular expression in a query?

[Konrad J Hambrick]

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]
-----------------------------------------------------------------------------