Re: [sqlite] How to specify regular expression in a query? ( Indexes usage issue)
Bharath Booshan L wrote: 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. I have tested that in sqlite3 command-line tool(v3.4.0), but no yield. REGEXP syntax is supported by SQLite, but you have to supply an suitable regexp function for SQLite to call when needed. That function is not included in the standard distribution. I have tried the following example to test the usage of index Create table MyTable(FilePath TEXT PRIMARY KEY NULL); Insert some appropriate values SELECT MovieURL FROM MyTable WHERE MovieURL = 'Some File Path'; Initially, I thought the Primary key in the table is automatically indexed, but that doesn't seem to be the case; Yes, the primary key field is automatically indexed. So I created an index on FilePath Create index indexFilePath on MyTable(FilePath); You are just duplicating the automatic primary key index. sqlite> explain query plan ...> SELECT MovieURL FROM MyTable WHERE FilePath = '/Volumes/Users/Shared/'; 0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1 Now what is this sqlite_autoindex_MyTable_1? Is it the index of implicit rowid of MyTable? It is the automatically generated index on the primary key. In simple way, shouldn't the above query use the index indexFilePath as it is FilePath is being compred with a constant? SQLite finds the automatic primary key index first and your duplicate index does not provide a better means of accessing the data so it is never used. Similarly, sqlite> explain query plan ...> SELECT MovieURL FROM MyTable WHERE MovieURL = '/Volumes/Users/Shared/%'; 0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1 Again, the wild character is at the end, and therefore it should have used index indexFilePath. Isn't it? This is an equality comparison,not a LIKE call so the % is a normal character, not a wildcard. In either case, the automatic primary keyu index is being used for the same reason as the previous test case. And one more sqlite> explain query plan ...> SELECT MovieURL FROM MyTable WHERE FilePath GLOB '/Volumes/Users/Shared/%'; 0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1 GLOB uses * and ? as wildcard characters, not %. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query? ( Indexes usage issue)
Thanks kjh for your valuable inputs, > If you use US ASCII, there is a collation (COLLATE NOCASE) > that could handle this for you. I am using Unicode characters. > 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. I have tested that in sqlite3 command-line tool(v3.4.0), but no yield. > CREATE TABLE t1 > ( >IDINTEGER, >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. No, The PathName and FileName has to be case sensitive. I have tried the following example to test the usage of index Create table MyTable(FilePath TEXT PRIMARY KEY NULL); Insert some appropriate values .. .. .. SELECT MovieURL FROM MyTable WHERE MovieURL = 'Some File Path'; Initially, I thought the Primary key in the table is automatically indexed, but that doesn't seem to be the case; So I created an index on FilePath Create index indexFilePath on MyTable(FilePath); sqlite> explain query plan ...> SELECT MovieURL FROM MyTable WHERE FilePath = '/Volumes/Users/Shared/'; 0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1 Now what is this sqlite_autoindex_MyTable_1? Is it the index of implicit rowid of MyTable? I have gone through the sqlite arechive of Indexes and its usage, but it made my knowledge on indexes even more complex. In simple way, shouldn't the above query use the index indexFilePath as it is FilePath is being compred with a constant? Similarly, sqlite> explain query plan ...> SELECT MovieURL FROM MyTable WHERE MovieURL = '/Volumes/Users/Shared/%'; 0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1 Again, the wild character is at the end, and therefore it should have used index indexFilePath. Isn't it? And one more sqlite> explain query plan ...> SELECT MovieURL FROM MyTable WHERE FilePath GLOB '/Volumes/Users/Shared/%'; 0|0|TABLE MyTable WITH INDEX sqlite_autoindex_MyTable_1 > I am not sure what your application is ultimately going to do. My App indexes certain movie files and custom annotations related to that movie file and stores that in a database. And at some point in time, App queries for Movie files under specific search directory along with constraints on these custom annotation. It is similar to that of a Spotlight Search in Mac OS X. > 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). More number of SELECT s are performed by the application and hence the right columns has to be indexed for better performance. Did I explained well? Any inputs will be greatly appreciated -- Bharath --- 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] -
Re: [sqlite] How to specify regular expression in a query?
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 ( IDINTEGER, 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] -
Re: [sqlite] How to specify regular expression in a query?
Hello experts, How can I instruct GLOB function to perform case-insensitive search similar to LIKE. Can I? -- Bharath On 1/30/08 10:30 AM, "Bharath Booshan L" <[EMAIL PROTECTED]> wrote: > Thanks for the inputs experts, > > I am using SQLite 3.4.0 on Mac OS X Leapord, and using regexp in my query > reports an error > > SQL error: no such function: regexp > > I tried to use the query using GLOB and another query which separates > FilePath into Dirpath and FileName and uses LIKE comparison. > > Both seems to be working fine. > > Is this the optimized query one could achieve for this problem? Is there any > other way which I could retrieve results much faster than the two queries I > have mentioned above? > > Thanks & Regards, > > Bharath > > > On 1/29/08 12:11 AM, "James Dennett" <[EMAIL PROTECTED]> > wrote: > >>> -Original Message- >>> From: Nicolas Williams [mailto:[EMAIL PROTECTED] >>> Sent: Monday, January 28, 2008 10:35 AM >>> To: sqlite-users@sqlite.org >>> Subject: Re: [sqlite] How to specify regular expression in a query? >>> >>> On Mon, Jan 28, 2008 at 06:22:08PM +0100, Ralf Junker wrote: >>>> I believe that this API would also ease implementations of Unicode >>>> LIKE and GLOB. >>> >>> That's what I was thinking of. The Unicode extensions work by >>> redefining the like, glob and regexp functions, and by adding >>> collations. But surely the existing user-defined functions interface >>> does not allow for this sort of optimization. >> >> Right, which is why this conversation is about extending that interface >> :) >> >>> Or did I miss something? >> >> No, I think you're in "violent agreement". >> >> -- James >> >> >> - >> 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] > - > > --- 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] -
Re: [sqlite] How to specify regular expression in a query?
Thanks for the inputs experts, I am using SQLite 3.4.0 on Mac OS X Leapord, and using regexp in my query reports an error SQL error: no such function: regexp I tried to use the query using GLOB and another query which separates FilePath into Dirpath and FileName and uses LIKE comparison. Both seems to be working fine. Is this the optimized query one could achieve for this problem? Is there any other way which I could retrieve results much faster than the two queries I have mentioned above? Thanks & Regards, Bharath On 1/29/08 12:11 AM, "James Dennett" <[EMAIL PROTECTED]> wrote: >> -Original Message- >> From: Nicolas Williams [mailto:[EMAIL PROTECTED] >> Sent: Monday, January 28, 2008 10:35 AM >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] How to specify regular expression in a query? >> >> On Mon, Jan 28, 2008 at 06:22:08PM +0100, Ralf Junker wrote: >>> I believe that this API would also ease implementations of Unicode >>> LIKE and GLOB. >> >> That's what I was thinking of. The Unicode extensions work by >> redefining the like, glob and regexp functions, and by adding >> collations. But surely the existing user-defined functions interface >> does not allow for this sort of optimization. > > Right, which is why this conversation is about extending that interface > :) > >> Or did I miss something? > > No, I think you're in "violent agreement". > > -- James > > > - > 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] -
RE: [sqlite] How to specify regular expression in a query?
> -Original Message- > From: Nicolas Williams [mailto:[EMAIL PROTECTED] > Sent: Monday, January 28, 2008 10:35 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How to specify regular expression in a query? > > On Mon, Jan 28, 2008 at 06:22:08PM +0100, Ralf Junker wrote: > > I believe that this API would also ease implementations of Unicode > > LIKE and GLOB. > > That's what I was thinking of. The Unicode extensions work by > redefining the like, glob and regexp functions, and by adding > collations. But surely the existing user-defined functions interface > does not allow for this sort of optimization. Right, which is why this conversation is about extending that interface :) > Or did I miss something? No, I think you're in "violent agreement". -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
On Mon, Jan 28, 2008 at 06:22:08PM +0100, Ralf Junker wrote: > I believe that this API would also ease implementations of Unicode > LIKE and GLOB. That's what I was thinking of. The Unicode extensions work by redefining the like, glob and regexp functions, and by adding collations. But surely the existing user-defined functions interface does not allow for this sort of optimization. Or did I miss something? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
Hello Nicolas Williams, >I suppose that to make this generic so that users can replace the >regexp, like, and glob functions would require some new interfaces. I believe so, too. The like and glob optimization is part of where.c and outside the reach of sqlite3_create_function(). >SQLite would have to be able to extract a constant prefix from the >pattern in order to be able to use an idex in this case. The regular expression engine I use is able to tell if a pattern is anchored at the beginning and which letter starts the pattern. I believe that this information is sufficient for an index to narrow down the search. SQLite just needs to provide the API to pass the prefix plus, possibly, which index to use. I believe that this API would also ease implementations of Unicode LIKE and GLOB. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
Nicolas Williams <[EMAIL PROTECTED]> wrote: > On Mon, Jan 28, 2008 at 10:07:01AM +0100, Ralf Junker wrote: > > Hello Bharath Booshan L, > > > > >>select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$'; > > > > > >Will this query use index, if we had one, on filepath? > > > > No. It will do a full table scan. > > But it could, no? > > I suppose that to make this generic so that users can replace the > regexp, like, and glob functions would require some new interfaces. > SQLite would have to be able to extract a constant prefix from the > pattern in order to be able to use an idex in this case. > Or, you could say: SELECT * FROM t WHERE filepath REGEXP '/MyMovie(\.[^\.]+)*$' AND filepath GLOB '/MyMovie.*' The second GLOB term will do the indexing. Then the REGEXP is applied to the all the terms that the GLOB matches to do a second layer of filtering. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
On Mon, Jan 28, 2008 at 10:07:01AM +0100, Ralf Junker wrote: > Hello Bharath Booshan L, > > >>select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$'; > > > >Will this query use index, if we had one, on filepath? > > No. It will do a full table scan. But it could, no? I suppose that to make this generic so that users can replace the regexp, like, and glob functions would require some new interfaces. SQLite would have to be able to extract a constant prefix from the pattern in order to be able to use an idex in this case. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
Hello Bharath Booshan L, >>select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$'; > >Will this query use index, if we had one, on filepath? No. It will do a full table scan. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
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] -
Re: [sqlite] How to specify regular expression in a query?
Thanks Ralf, >select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$'; Will this query use index, if we had one, on filepath? Regards, Bharath On 1/25/08 5:22 PM, "Ralf Junker" <[EMAIL PROTECTED]> wrote: > Hello Bharath Booshan L, > > yes, with SQLiteSpy you can do this: > > drop table if exists t; > create table t (id integer primary key, filepath text); > insert into t values (1, '/Volumes/Backup/MyMovies/MyMovie.mp4'); > insert into t values (2, '/Volumes/Backup/MyMovies/Hello.mp4'); > insert into t values (3, '/Volumes/Tiger/MyMovie.mov'); > select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$'; > > Ralf > >> 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. > > > - > 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] -
Re: [sqlite] How to specify regular expression in a query?
your example only needs a "like" not a regex. not having your schema i'll show a loose example: select id, flilepath from movies where filepath like "%MyMovie.%"; 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. Thanks in advance, Bharath --- 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] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
Hi! Why don't try with LIKE comparison: select * from table where column LIKE '%MyMovie%'; Bharath Booshan L escribió: 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. Thanks in advance, Bharath --- 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] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
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] -
Re: [sqlite] How to specify regular expression in a query?
Hello Bharath Booshan L, yes, with SQLiteSpy you can do this: drop table if exists t; create table t (id integer primary key, filepath text); insert into t values (1, '/Volumes/Backup/MyMovies/MyMovie.mp4'); insert into t values (2, '/Volumes/Backup/MyMovies/Hello.mp4'); insert into t values (3, '/Volumes/Tiger/MyMovie.mov'); select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$'; Ralf > 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. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to specify regular expression in a query?
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. Thanks in advance, Bharath --- 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] -
