Thanks kjh, Sorry, there are some missing details which I did not explain.
1. Search should be something like MyMovie*.* 2. I have to search for filename under specified volume. i.e. Search MyMovie*.* in volume '/Volumes/Backup' 3. I already have a Movie table with AutoIncrement primary key. > > create table MovieFiles > ( > ID integer, > FilePath varchar(255), -- contains `dirname MovieFile` > FileName varchar(255) -- contains `basename MovieFile` > ) ; > create index MovieFilesFileName on MovieFiles( FileName ) ; With above details in consideration, if I have to split filepath into dirname and basename and index FileName, then would the following query uses the indexes properly? Select * from MyMovies where FilePath LIKE '/Volumes/Backup/%' AND FileName LIKE 'MyMovie%'; Or something like this using regular expression and FilePath column contains both dirname and basename Select * from MyMovies where FilePath regexp '/Volumes/Backup/*/MyMovie*(\.[^\./]+)*$'; Using regexp in query prevents use of index I suppose, Isn't it? --Bharath On 1/25/08 6:15 PM, "Konrad J Hambrick" <[EMAIL PROTECTED]> wrote: > > > 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] > ----------------------------------------------------------------------------- > > ----------------------------------------------- Robosoft Technologies - Come home to Technology Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
