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

[Konrad J Hambrick]

On 01/26/2008 02:40 AM, Bharath Booshan L wrote:
>

Hello list,

 I have to perform a search something similar to this

  ID     FilePath
   1      /Volumes/Backup/MyMovies/MyMovie.mp4
   2      /Volumes/Backup/MyMovies/Hello.mp4
   3      /Volumes/Tiger/MyMovie.mov


Search for file name MyMovie should retrieve

ID FilePath

   1      /Volumes/Backup/MyMovies/MyMovie.mp4
   3      /Volumes/Tiger/MyMovie.mov


To simplify, I am searching for a file name from a collection of absolute
file paths.

How can I achieve this in SQLite?

Is there anyways I can use regular expression in a query to perform string
matching.

Bharath --

I suppose if say, your Movie Table is called 'MyMovies'
then, I you could:

    -- the following assumes all files have a 3-char extent

   select ID,
          FilePath
     from MyMovies
    where FilePath glob '*/MyMovie.???'

Or (more portably but less precisely) ...

   select ID,
          FilePath
     from MyMovies
    where FilePath like '%/MyMovie.%'

This could be slow on a large Table.

Have you thought about splitting the Path (man dirname)
from the FileName (man basename) ?

Something like:

   create table MovieFiles
   (
      ID  integer,
      FilePath  varchar(255), -- contains `dirname  MovieFile`
      FileName  varchar(255)  -- contains `basename MovieFile`
   ) ;

Might make for simpler queries and faster too if you
add an index on the FileName Column.

   create index MovieFilesFileName on MovieFiles( FileName ) ;

HTH

-- kjh




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------