I do this in a 2 stage thing as part of an AJAX lookup
this is the CF code, #SUGGEST_LOOKUP_STR# is the input string
OUTLOOKNAME is a field in the DB made of firstName Surname, CompanyName (or
variations thereof) like in Outlook.
Result if you type in "t"
returns all items with any word starting in t
type in "t f"
returns all items which have words starting in t and f
type in "tr f"
returns all items which have words starting in tr and f
Etc.
--------------------
<CFLOOP LIST="#trim(replace(SUGGEST_LOOKUP_STR,"'","''","all"))#"
DELIMITERS=" " INDEX="idx">
<CFSET qstr =
listappend(qstr,"(dbo.UDF_MAKESEARCHSTRING(OUTLOOKNAME) like
'%|#idx#%')","¬")>
</CFLOOP>
<CFSET qstr = replace(qstr,"¬"," AND ","all")>
<CFQUERY NAME="getlist" DATASOURCE="#ds1#">
SELECT USERREF AS RETURNID, OUTLOOKNAME AS DIVDATA
FROM ALLCONTACTS
WHERE
ISCUSTOMER = 1 AND
#preservesinglequotes(qstr)#
ORDER BY OUTLOOKNAME
</CFQUERY>
-------------------
and the UDF in MSSQL is:
-----------------------
USE [MY_DB]
GO
/****** Object: UserDefinedFunction [dbo].[UDF_MAKESEARCHSTRING] ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[UDF_MAKESEARCHSTRING] (@mystring varchar(700))
RETURNS varchar(700) AS
BEGIN
-- strip out ( and ) and , from search string
SELECT @mystring = replace(@mystring,'(','')
SELECT @mystring = replace(@mystring,')','')
SELECT @mystring = replace(@mystring,',','')
SELECT @mystring = replace(ltrim(rtrim(@mystring)),' ','|')
if len(@mystring) > 1
begin
SELECT @mystring = '|' + @mystring
end
RETURN @mystring
END
-----------------------
HTH
Richard
> -----Original Message-----
> From: Les Mizzell [mailto:[EMAIL PROTECTED]
> Sent: 26 February 2008 16:34
> To: CF-Talk
> Subject: Searching for keywords from an entered list...
>
> I'm wanting to modify a site search (field in SQL Server
> database) so that in a single input field, a user could enter:
>
> 1. red
> 2. red, blue, green, brown
> 3. red green blue brown
>
> .... and in return, get all results that contained
> one/some/all of the entered terms.
>
> In theory, what would be the best way to go about this?
>
> There was a previous thread on this, but so far, I've been
> unable to find it. Anybody got the URL of the original thread?
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299900
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
