-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I am attempting to refine a query to match a smaller number of records, and I
believe that regular expressions are the way to go. However, I have very
little experience with them, and could use some help figuring out where to
insert my regex. I am doing the following to prepare a variable for the query:
<!--- create criteria string --->
<cfif listlen(lstSearch) is not 0>
<cfset Criteria = "(">
<!--- compare product name --->
<cfset lcount = 0>
<cfloop index="word" list="#lstSearch#">
<cfset lcount = lcount + 1>
<cfif lcount is not 1>
<cfset Criteria = Criteria & " OR ">
</cfif>
<cfset Criteria = Criteria & " tblProduct.productName like '%#word#%' ">
</cfloop>
<cfset Criteria = Criteria & " OR ">
<!--- compare product code --->
<cfset lcount = 0>
<cfloop index="word" list="#lstSearch#">
<cfset lcount = lcount + 1>
<cfif lcount is not 1>
<cfset Criteria = Criteria & " OR ">
</cfif>
<cfset Criteria = Criteria & " tblProduct.productCode like '%#word#%' ">
</cfloop>
<cfset Criteria = Criteria & ")">
</cfif>
the query that uses that is:
<!--- Get products matching search criteria for display --->
<cfquery name="GetProduct" datasource="#application.dsn#">
SELECT tblProduct.productId,
tblProduct.productName,
tblProduct.productSize,
tblProduct.productImg1URL,
tblProduct.productImg1Name,
tblProduct.productRetailPrice,
tblProduct.productCode
FROM tblProduct
WHERE tblproduct.productInactive = false
<cfif Criteria is not "">
AND #preservesinglequotes(Criteria)#
</cfif>
ORDER BY tblProduct.productName;
</cfquery>
so, should I replace the '%#word#%' with a regex (i assume this would
require regex support in the db (?)) or do I add some code after the query to
use my regex on the results of the query?
The effect I am attempting to acheive is to allow the #word# variable to be:
[[:space:][:punct:]]#word#[[:space:][:punct:]], I believe (any space or
punctuation character before or after, but no alpha-numeric chars.
any advice, sample code, etc would be much appreciated.
- --
Jon Tillman
http://www.eruditum.org
The original point and click interface was a Smith & Wesson.
-----BEGIN PGP SIGNATURE-----
Version: PGP 6.5.1i
iQA/AwUBOtRu49ga7tZtnIOtEQKs0QCfbkjwQ6HH7DwYlk8K1M3mApO2e2gAoJZo
N7qchw2DF//q7W4ZoZUmsmb5
=Op/g
-----END PGP SIGNATURE-----
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
