RB Smissaert wrote: > Have 2 tables with both one text field called term and need to run a > SQL like this, to count the records in table1 where the start of term > in table1 equals a term in table2: > > select > count(a.rowid) > from table1 a inner join table2 b on > (lower(b.term) = lower(substr(a.term,1,length(b.term))))
Try writing the condition as a.term collate nocase between b.term and b.term || 'Z' Replace 'Z' with a character that compares above any characters that may appear in your strings. If in doubt, try using CAST(X'EFBFBF' as text) (this is U+FFFF represented in UTF-8, and should compare greater than anything valid). Make sure there is an index with collate nocase on a.term (an index on b.term won't be helpful). Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
