Bruno Moreira Guedes wrote: > > I can split my 'something' in st0, st1, st2, stN... I have a '.' > betwen this values. But the number of "st"s is undefined. I tried to > do "the necessary" number comparsions, appending tokens: > > SELECT fields FROM sometable WHERE field = 'st0.st1.st2.st3' OR field > = 'st1.st2.st3' OR field = 'st2.st3' OR field = 'st3'; >
You could try this select fields from sometable where substr(field, -length(:somestring)) = :somestring; This will still require a full table scan and will not use an index, but the overhead of testing if the field ends with the appropriate string should be as small as possible. Your main problem is there is no way to use an index to match the end of a string. If this is a common operation for you database, you may want to add a field that stores the strings in reverse order. You can then add an index on that string. What used to be the end of the string is now the beginning of the reversed field, and can be searched quickly using an index. You will need to create a user defined function to reverse the characters of a string. reverse('string') => 'gnirts' With this function you could add a new field to your database and create a suitable index on that field. alter table sometable add column reversed text; update sometable set reversed = reverse(field); create index field_reversed_idx on sometable(reversed); Now you can use the same function to reverse the string you are trying to match and use a like comparison to locate the strings quickly using the index (since the search string is now the prefix of string). select field from sometable where reversed like reverse(:somestring) || '%'; HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users