I did this for an FAQ not long ago... something like this worked for me
select dictionary.word
from dictionary
inner join dreams on
dreams.dream like '%' + dictionary.word + '%'
order by dictionary.word
In the case of my faq the user was entering another question and the text of
the question was compared against the dictionary and then again compared to
other questions, so if you've got someone entering a new dream, you could
put this in a stored procedure like
CREATE PROCEDURE sp_DreamNewLookup
@txt_dream nvarchar(8000)
AS
select dictionary.word, dreams.dream from dictionary
inner join dreams on dreams.dream like '%' + dictionary.word + '%'
where @txt_dream like '%' + dictionary.word + '%'
order by dictionary.word
GO
hth
s. isaac dealey 954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
> I'm not a SQL wizard and am doing this off the top of my
> head, but I'd try
> something along the lines of
> select word from dictionary where
> (select dream from dreams where dream_id = #dreamid#)
> like ('%' + word + '%')
> But, again, that's off the top of my head.
> --Ben Doom
> Programmer & General Lackey
> Moonbow Software
> : -----Original Message-----
> : From: Kelly Matthews [mailto:[EMAIL PROTECTED]]
> : Sent: Thursday, December 19, 2002 11:38 AM
> : To: CF-Talk
> : Subject: Is there a Better Way to do this?
> :
> :
> : Ok here is what i am doing and I am just trying to find
> out if 1.
> : there is a
> : better way and 2. if it can be done on the SQL side (as
> a stored proc)
> : instaed of the CF side. Just not all that familiar with
> looping
> : outside of
> : CF.
> :
> : ANyway I have one table that is a dictionary with about
> 1500
> : words. THen I
> : have another table that has dreams people have entered.
> THe idea
> : is to take
> : the list of words and see if any of them show up in the
> dream and then
> : display those words to the user. What I built works and
> it's
> : actually pretty
> : speedy, but the dictionary is very small right now and
> it may be
> : closer to
> : 10000 by the time we are done which is a MUCH larger
> list of
> : words to loop
> : through.
> :
> : So right now I am doing a query to get the words
> : <CFQUERY name="wordS">
> : SELECT word
> : from dictionary
> : </CFQUERY>
> :
> : THen we grab a dream
> : <CFQUERY name="dream">
> : SELECT dream
> : from dreams
> : where dream_id = 3
> : </CFQUERY>
> :
> : Then I Loop through like so:
> : <CFLOOP list="#valuelist(words.word)#" delimiters=","
> index="theword">
> : Then I check to see if the dream contains the word.
> : <CFIF dream.dream contains " #theword# " OR dream.dream
> contains "
> : #theword#s>
> : Then I grab the definition if it found that word.
> : <CFQUERY name="getdef">
> : SELECT definition from dictionary
> : where word = '#theword#'
> : </CFQUERY>
> : Then I display it #theword#<BR>#getdef.definition#<P>
> : </CFIF>
> : </CFLOOP>
> :
> : This words fine and with a dream thats a few thousand
> words it
> : completes in
> : a few seconds. I just wonder if there is a faster or
> better way
> : to do this
> : so down the road we don't run into problems as the word
> list grows.
> : THoughts?
> :
> :
> :
> :
> :
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> ~~~~~~~~~~~|
> Archives:
> http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
> Subscription: http://www.houseoffusion.com/cf_lists/index.
> cfm?method=subscribe&forumid=4
> FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
> Structure your ColdFusion code with Fusebox. Get the
> official book at http://www.fusionauthority.com/bkinfo.cfm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
