>>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.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only