On 07/12/2012 03:17 PM, peixinhosdalua wrote:

> Regarding your suggestions, i need to make a search by '%word%' not 'word%'. For example, most companies have a commercial name or brand that they use and these are different from the juridic name. It is the juridic name that is inserted in the table because this table is using to make invoices. So it is standard to have companies names like: Something Brand LTD and people search for Brand and if they do it like 'Brand%' never appears.
>
> Same can happen for phones if you search with out without prefixes. Also the VAT-ID. For example VAT's in Europe have letters at beginning but most people omitted the letters when searching for VAT-ID from their own country.
>
> This is even more true when searching for products.
>
> So, how can i improve the usage of the like or containing with the %word%?

You may try to create an extra search-table sth. like
(searchword,tablename,fieldname,ID) and fill this on insert,update or
delete using triggers on your original tables,
Create one or more stored procedures that do a
for select tablename,id from searchtable where searchword like
'%whatever%' into ..
(maybe trying a starting with first)

and returns the values you want (via execute statement) .

This will reduce the table scan to one table.

hth
fsg

--
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
their tears have healing powers and they make highly faithful pets."
- J.K. Rowling