Indexing a Txt field

[Bob Scarborough]

G'day,
I have a products table which contains products from 4 vendors. The records have the following fields "Vendor", "Vendor Part Number"," Description", "Unit" and "Price"
I have indexed same by Vendor Part Number and Description.

The vendor's describe the same product using different descriptions.
VENDOR ....DESCRIPTION............................................PART NUMBER
Acme........TEKS METAL HEX+SEAL 10X16MM 100 (PKT)..123456-01
Banner.......METAL TEKS 10X16 MM..............................93a-1234

If I search by part number the search is extremely fast.
However a search on the DESCRIPTION field looking for individual words say 'Like TEKS*' and 'Like METAL*' are very slow. Obviously the indexing on the description field is probable based on sorting the 1st character of the Description field by A-Z then the second character etc.

I am wondering is there a smart way of indexing the Description field at the point of importing or creation of the Products Table where I could identify all the records with say 'TEKS' or 'METAL' or '10X16' etc. By doing this the search should be a lot faster.

Has anyone done this or do you have a pointer to an article that covers this. (I did try the search function without a suitable result)

Thanks in advance

 

[Len Boorman]

Do not think you can detaermine an Index based upon part of a field contents.

What you may consider is breaking up the description into relevant attributes. You could always get back to the full description by concatonating the parts. That would enable indexing and fast searching. Could be a drag job to set it up to begin with and you would most certainly need some sort of convention of a description

L

 

[gemma-the-husky]

cant you get the products into smaller groups by providing combo boxes to select attributes - eg product types, product groups etc etc.

Hopefully, you will end up with a fairly modest final selection group.

 

[Bob Scarborough]

Thanks Len and Jemma for your suggestions. Both of the my primary Vendors do have product groupings but as in the case of the product descriptions the groupings do not match nor cover the same group of products.

I get monthly feeds from them of the product description, part number and price but I have found that from month to month even then the description often will change for the same Vendor

I was keen to adopt something along the line of what Len suggested but was unsure how to set about doing it. I was envisioning preprocessing each product record as it was supplied each month and doing the necessary splitting, concatenating and indexing prior to putting it in the lookup table.
Again I'm not quite where to start on this.
Any suggestions?