It is my understanding that if I use a wildcard at the front of the string then it will cause a linear table search rather than using the index if the string is the first part of the match. For example, "... col like '%String'" or "... col like '%String%'" would cause a linear table search ignoring the index. As opposed to "... col like 'String%'" which would still use the index for matching. This is assuming that col is an indexed field. Is this a correct understanding?
Also, would the following only search part of the table in a linear fashion? "select * from table where id >= 150 and id <= 1500 and col like '%String%';" This assumes that id and col are key and index respectively. Thanks in advance for your patients and help. J. R. -------------------- J. R. Westmoreland E-mail: j...@jrw.org -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Monday, February 02, 2009 10:37 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Question on the "like" item in "select" On Mon, Feb 2, 2009 at 12:33 PM, J. R. Westmoreland <j...@jrw.org> wrote: > Please pardon me if this is really more SQL rather than sqlite behavior. > > > > If I have some thing like: > > SELECT . WHERE . LIKE "String" .; > > Is it going to work like: > > SELECT . WHERE . = "String" .; > > LIKE is case-insensitive, and typically used with wild cards. WHERE col = 'String' would be case-sensitive and only match 'String' whereas LIKE will match 'String' and 'string' Compare with GLOB which is case-sensitive. In either case, you want to use single-quotes, not double-quotes. > > Thanks, > > J. R. > > > > -------------------- > > J. R. Westmoreland > > E-mail: j...@jrw.org > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
