>>>what is the fastest method to search for strings contained within a field using SQL? For example, I currently use:
>>>
>>>(SIMPLE EXAMPLE)
>>>
>>>ownername field might contain -> HAROLD D. SMITHE
>>>I want every record that contains the name "SMITH" in table1
>>>
>>>I am doing this:
>>>
>>>SELECT OWNERNAME from table1 ;
>>>WHERE ownername like '%SMITH%'
>>>
>>>I do have and index on the ownername field.
>>>
>>>This takes quite sometime to get resultant data back on my 70,000 record table. Is there a more optimized way of doing string searches than the way I'm doing it?
>>Hi,
>>
>>Not a very constructive answer, but you can have it anyway...
>>
>>Because the DB has to search through every character position to find your string, It is impossible to create any index to speed up this search.
>>
>>If possible, you should try to get the data into a more useful format. If this is really important to you, and you can spare the disk space, you could create a subtable of the Owner and strip out each word from the name, inserting each one as a separate record in the 'NameSearch' table. Then
>>
>>SELECT OWNERNAME from table1, NameSerach ;
>>Where table1.id = namesearch.OwnerId AND ;
>>namesearch.key = "Smith"
>
>Hi Mark, thanks for responding!
>
>This table is quite sizeable and I do searches for several fields this way.
>I'm not sure if this method would work for me. Good idea though. I certainly wouldn't have thought of that. I heard there was a third party package that does this kind of search pretty well, but I can't remember what the name of it was.
>
>Thanks for your idea!
>
>john.

I think that I remember Xitech having something along those lines, but for the contents of memo fields.