>>>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.
John,
I don't have it but used evaluation for 2.x and 3.0 sometime ago. phDbase3 (phdbase part is correct as I remember, rest may be wrong) is a powerfull char and memo search (yes memo I wrote) third party tool. I really want to have it too one day. It not only searches text and memo lightning speed (all were instant in my evaluation), but has options such as include digits, support searches like "disk near copression", "disk within 5 words compressed" etc. Syntax I used here are not the original ones but I mean you could search ie: both "disk" and "compressed" where they're max 5 words apart in memo (and the results are instant !). It has many more options. At least search for it and do an evaluation. I think you also would have a discount for it being a UT member.
Cetin