CFQUERY Advance Where'ing, or catching on output?

[webmigit]

Hi guys... here's a dumb question...

I've got a KJV Version of the bible in Access Format, there are 31,102 verses in the KJV (and most other versions...)

I'm having a little problem, I want to use regex in a query and I can't get it to.. but I want ACCURATE matches.. like...

"... God said let there be light ..." should not be returned if someone searches for "here".. but should be returned if someone searches for a whole word in that.. I already have the cfoutput working properly.. but the question is this... If someone searches "he", well "he" appears in "the", "they", "she", "them", etc.. so my query will return all these.. The cfoutput (or cfloop) will filter these out... will my search be hurt if the query returns a lot of results but only a few are outputted? I mean where is the real load bearing weight of a page? In the query or the assembling of the data.. I'd imagine the query, no?

SQL is so weak about regex.. a sad thing.. the closest thing to regex anyone seems to know about is the %wildcard characters%...

Tony

 

[SidianConsulting]

The load bearing weight in this case is the data output. If you were to return thousands of results and had to parse through each with regex cf functions, it would be very slow. Why not just search for each input word? If someone searches for "he", and "they" and "here" are being returned, you are doing a wildcard match like '%he%' where you should be matching just 'he'. Otherwise I guess I am not understanding your question.

-Tek

 

[philcha]

In programming terms the easy way is to use the Verity search engine. The cost is disk space - Verity is a fully-inverted list system and will read the DB and build its own index containing every word except the "noise" words (and, the, etc.). A search returns the keys of the matching records in CFQUERY format so you can look up the verses in the DB.

Last time I looked Maryland CF Users Group (

http://www.cfug-md.org/)

had an excellent tutorial on Verity. I read it and had my first Verity index and search page up in an hour - that's a tribute to the tutorial, not to me! And you can copy about half of the tutorial as a help page for the users.

 

[r937]

tek, you said tony was doing a wildcard match like '%he%' and should instead be matching just 'he'

that won't work, you can't match on 'he' (without wildcards) because that will only return rows where the value is 'he' i.e. only two characters long

tony's problem is searching for whole words

the wrinkle with LIKE is that you cannot just search for it using '% word %' (i.e. the whole word with spaces around it), because the end of the whole word could be followed by a comma, question mark, period, etc., and those would not be included in the results

i've seen tony look for help in other forums but so far, nothing has surfaced to match the capabilities of regex

(i had suggested a more sophisticated LIKE string, which tony may yet decide to incorporate along with a cf regex filter)

verity is actually a very good idea for you, tony, now that you've described what's in your database -- the drawback to verity is that it quickly falls out of date if there's a lot of churn in the data, but yours sounds like it won't need updating at all

rudy

http://rudy.ca/

 

[webmigit]

faq232-2318

Sorry guys, had numerous threads in forums on this site and others... missed this thread...

The above is a faq I wrote involving the solution...

Tony

 

[r937]

that faq is great -- i really like (no pun intended) your LIKE clause

dbField LIKE '%[^A-Za-z0-9]word[^A-Za-z0-9]%'

your faq says MS requires an exclamation point, but i thinks that's just msaccess -- the caret is valid in sql/server

nice writeup

 

[webmigit]

Heh, glad you liked it... hey.. mind rating on the 1-10 thing at the bottom?

Tek Tips is about developers helping developers whenever they can.. and although I got lucky in figuring out that faq.. I've gotten a lot of inspiration from tek tips.. that's my way of helping others out.

Tony

PS: If you'll let me know your url again, I'll email you my code.. what I posted was a very simplified version...

 

[r937]

thanks tony, yes, i'd love to see your code

http://rudy.ca/