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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
