2008/9/9 Dennis Cote <[EMAIL PROTECTED]>:
> 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;
>
I need to check if:
'.' || field
is a substring of something, and if something
But it should be useful. Thank you!
> 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
>
Yes, it's the perfect solution!! I'll work to implement this. Thank you again!!
Thank you all,
Bruno
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
