It's not pretty and missing cfqueryparams but ...
<cfset criteria = "AUTHOR='GIESEMAN','SMITH', ABSTRACT='CAT','DOG','FISH'">
<cfset author = replace(listgetat(criteria, 2, "="), ", ABSTRACT", "")>
<cfset abstract = listlast(criteria, "=")>
<cfquery>
SELECT whatever from tbl_something
<cfif listlen(author)>
where
(
<cfloop from="1" to="#listlen(author)#" index="i">
author LIKE %#listgetat(author, i)#%
<cfif i neq listlen(author)>or </cfif>
</cfloop>
</cfif>
<cfif listlen(abstract)>
<cfif listlen(author) and listlen(abstract)>or</cfif>
<cfloop from="1" to="#listlen(abstract)#" index="i">
abstract LIKE %#listgetat(abstract, i)#%
<cfif i neq listlen(abstract)>or</cfif>
</cfloop>
)
</cfif>
</cfquery>
should spit this out...
SELECT whatever from tbl_something
where
(
author LIKE %'GIESEMAN'%
or
author LIKE %'SMITH'%
or
abstract LIKE %'CAT'%
or
abstract LIKE %'DOG'%
or
abstract LIKE %'FISH'%
)
Would probably break if one of the lists were empty, I didn�t try that. But
it shouldn�t be hard to try out.
-----Original Message-----
From: Gieseman, Athelene [mailto:[EMAIL PROTECTED]
Sent: Friday, April 15, 2005 4:29 PM
To: CF-Talk
Subject: Need Help Parsing Text
I have a text field that contains saved search criteria. The field is
called CRITERIA. I need to take the criteria in the field and make a valid
SQL query out of it. For example, I have a record in the table that has the
following in the CRITERIA field:
My string is: AUTHOR='GIESEMAN','SMITH', ABSTRACT='CAT','DOG','FISH'
I want to find all the records that have either GIESEMAN or SMITH as the
author AND where abstract contains either CAT, DOG or FISH.
How can I auto-magically parse the string into a valid SQL query?
Athelene Gieseman
Chief Information Officer
Stinson Morrison Hecker
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
This communication is from a law firm and may contain confidential and/or
privileged information. If it has been sent to you in error, please contact
the sender for instructions concerning return or destruction, and do not use
or disclose the contents to others.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:203115
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
