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
