I need to search for string prefix matches; for example given the path 'PP', I need to find 'PPA', 'PPBJQ', and 'PPz'. (The character set is all characters greater than or equal to 'A', and is case-sensitive.) A statement that does exactly what I want is
"UPDATE item SET hasChildren = EXISTS (SELECT path FROM item AS c WHERE substr(c.path, 1, length(item.path)) == item.path AND length(c.path) > length(item.path)) WHERE path = ? OR path = ?" I used to use "UPDATE item SET hasChildren = EXISTS (SELECT path FROM item AS c WHERE c.path GLOB item.path || '?') WHERE path = ? OR path = ?" (These searches are slightly different, since the first one finds all items which start with either given string, and the second finds only the immediate children. In this example, they produce the same result.) I can't use the second statement, because GLOB uses brackets as wildcards (asterisk and question mark are not present in my strings). However, GLOB would be faster, because of the index optimization ( http://www.sqlite.org/optoverview.html section 4.0). When I'm directly querying for items, instead of using the EXISTS expression, I can do something like this (in JavaScript, using the HTML5 relational database API): nextPath = project.path.slice(0, -1) + String.fromCharCode(project.path. charCodeAt(project.path.length-1) + 1); transaction.executeSql("SELECT dbId, path, summaryText, dueDate FROM item WHERE path >= ? AND path < ? AND dueDate IS NOT NULL", [project.path,nextPath], ... Is there a faster statement that does what I want (my first SQL statement above) in pure SQL? -- Doug Reeder reeder...@gmail.com http://reeder29.livejournal.com/ https://twitter.com/reeder29 https://twitter.com/hominidsoftware http://outlinetracker.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
