Yea, there's a couple things you can do ...
Make sure that every word in the dream is bounded by spaces
You can do this with a couple regular expressions like this:
<cfset dreamtext = REReplace(dreamtext,"([[:alpha:]])([^ [:alpha:]])","\1
\2","ALL")>
<cfset dreamtext = REReplace(dreamtext,"([^ [:alpha:]])([[:alpha:]])","\1
\2","ALL")>
To give you a quick run-down ( in case you're unfamiliar with regular
expressions ), this says find an alphabetic character [[:alpha:]] followed
by a character that is not a space and not alphabetical [^ [:alpha:]], and
replace them with the first character \1 a space and the second character \2
-- it then reverses the position of the alpha and the non alpha characters
and places spaces in front of words which are prepended with something other
than a space.
Then feed your massaged dream text into the stored procedure with an extra
space on either side of the text and add the spaces to the query like so:
WHERE dreams.dream LIKE '% ' + dictionary.word + ' %'
Alternatively, you could just change anything that's not a space into a
space before you feed it to the stored procedure like this:
<cfquery ...>
exec sp_DreamNewLookup
@txt_dream = ' #REReplace(dreamtext,"[^ [:alpha:]]"," ","ALL")# ';
</cfquery>
This is going to also replace numbers and punctuation, however, so you
probably won't want to store the text after this REReplace() function call.
If you need / want more help with regular expressions, check out the
cf-regex list also on www.houseoffusion.com
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
> No you were correct I only want to pull the dictionary
> words that were found
> in the dream. The code you gave me worked great the only
> issue i see now for
> example let's say the word is FEEL then it pulls EEL out
> of the dictionary.
> IS there a way to avoid that?
>> >> 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.
>> >>
>>
>> > What you mean is :
>>
>> > SELECT dictionary.word, dictionary.definition
>> > FROM dictionary LEFT JOIN dream
>> > ON dreams.dream LIKE '%' + dictionary.word + '%'
>>
>> > Which is what Isaac's got in his stored proc... ;o)
>>
>>Accept that if you left join dream, you'll get every word
>>from the
>>dictionary -- I was under the impression ( possibly
>>mistaken ) he was
>>looking for only words appearing in the dream text ...
>>
>>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
>>
>>
>>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> ~~~~~~~~~~~|
> 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
> Signup for the Fusion Authority news alert and keep up
> with the latest news in ColdFusion and related topics.
> http://www.fusionauthority.com/signup.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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
