>>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.