You can use conText in oracle 8i. That'll give you fuzzy matches and stemming and such. You basically set up an index on the column (columns) you want to search and then you use the Contains keyword.
On 7/29/05, Matt Osbun <[EMAIL PROTECTED]> wrote: > That's trolling as in "Fishing", rather than "Being nasty just to get a > rise out of people" ;) > > I have a need to create some sort of keyword-searchable index for a CF > application, so that the user can enter a person's first and last name > into a form and search on it. I'm not particular as to what kind, just > something better than various permutations of > "where lastname like '%#form.lastname#%' and firstname like > '#form.firstname#%'..." > > The data we get in is from various thrid parties, and is choick full o' > errors- misspellings, lastname in the firstname column, first name > omitted, etc. Unless someone knows better- and I'm not claiming to be > an expert- a database full-text index or a Verity collection seems to be > the way to go. > > Problem is, the data is stored in an Oracle 8i database which, to the > best of my knowledge and efforts, does not support full-text indexing. > > Second problem: Indexing a Verity collection is taking about 3 hours. > I've only used Verity to index files in a folder, but I don't remember > it taking near this long to do- even with folders with large numbers of > files. There will be times when the collection would need to be > reindexed over the course of the day- three hours is way too long. > > So first of all, here's the query and code I'm using to index the > collection. Am I doing something incorrectly that is causing such a > long index time? > > <cfquery name="qryNames" datasource="datasource"> > select id, trim(firstname)|| ' ' || trim(lastname) AS FullName > FROM people > WHERE level1org=2000 and status='a' > </cfquery> > > <cfindex collection="names" action="update" title="Names" key="id" > body="id,FullName" type="Custom" query="qryNames" > status="verResults"> > > The query returns just over 48,000 rows, and is using an index on > Level1org and status, FWIW. > > Barring someone pointing out something I've missed with the Verity > collection or presenting some other method that I haven't thought of, my > only other idea is to schedule a DTS package to import the table into > SQL Server and create a full-text index there. The DTS Package would > have to run periodically thoughout the day to catch any updates or new > records in the Oracle table, which would also mean reindexing the SQL > Server full-text index. There ought to be a better solution than that. > > Just for the record, permanantly storing the People table in SQL Server > isn't an option, nor is an Oracle upgrade. > > Incidently- the most recent <cfindex> finished up while writing this. > Start time 07:41:36 Finish Time: 10:25:34. > > Matt Osbun > Web Developer > Health Systems, International > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213276 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
