Search queries with single quotes

[thanesherrington]

I'm doing a QoQ for last names and sometimes I run into O'Briens or O'Tooles and these won't search.

I'm using:

<cfset Variables.SearchName="%"&LCase(Trim(Form.Name))&"%">

(LOWER(LastName) LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Variables.SearchName#">

Any idea what I'm doing wrong?

[Charlie Arehart]

Use the preservesinglequotes function, within your cfset, alongside the trim function. Let us know if it helps. 

[thanesherrington]

I think I've found my problem. 

I was using:

WHERE ((LOWER(LastName) LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Variables.SearchName#">)

When Variables.SearchName="%smith%" or "%smi%" this works fine.

But if Variables.SearchName = "%o'brien%" or "%o'b%" it fails.  (Even with your idea of preservesinglequotes).

If I switch to:

WHERE ((LOWER(LastName) = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Variables.SearchName#">)

And Variables.SearchName = "o'brien" the it finds all O'Briens.

I'm using CF9 and and MS Access database. 

Any other ideas how I might get the wild cards working with names with apostrophes?

[Charlie Arehart]

Well, the reason that it works when you do the = (rather than like) is that then you're simply passing in a variable value to that =  test, and CF by default does do the equivalent of preservesinglequotes for you. The function is really for when you "build the SQL" outside the query and store it in a variable that you then refer to within the cfquery. Then it does NOT do the preservesinglequotes for you, and that's why you may need to add that in the code that is BUILDING that sql outside the cfquery.

And I thought that perhaps since you were adding the % in the cfset, that may lead CF to therefore require you to add the function.

But now when you say you have changed to just doing the =, are you really meaning that you have left the % in the CFSET? I would not expect ANY of the values to match in that case, since none of your table cols would have that % in the value (and = is indeed an equality match). Maybe you mean that you ALSO changed the CFSET to not use the % (even though you don't say so).

So why don't you try this:

WHERE ((LOWER(LastName) LIKE '%<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Variables.SearchName#">%')

Notice I have taken the like out of the cfset and put it in the SQL (where really it belongs). I am not sure it WILL work, but you have presuming you could put that in the variable that is passed to the cfqueryparam, and I would not be surprised if it did not work.

I proposed the preservesinglequotes as a first possible solution. Let us know if this second one may work for you.

[thanesherrington]

Ok, here's what I'm using now:

<cfset Variables.SearchName = LCase(preserveSingleQuotes(Form.Name))>
<cfset Variables.StrictSearchName = LCase(preserveSingleQuotes(Form.Name))>

<cfquery dbtype="query" name="GetInfoForDisplay">
SELECT FirstName, LastName, Street, Phone
FROM FilterQuery
WHERE ((LOWER(LastName) LIKE '%<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Variables.SearchName#">%')
ORDER BY LastName, FirstName
</cfquery>

<cfif GetInfoForDisplay.RecordCount EQ 0>
<cfquery dbtype="query" name="GetInfoForDisplay">
SELECT FirstName, LastName, Street, Phone
FROM FilterQuery
WHERE ((LOWER(LastName) = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Variables.StrictSearchName#">)
ORDER BY LastName, FirstName
</cfquery>
</cfif>

Using this code:

"smith" or "Smi" finds all Smiths

"O'Brien" finds all O'Briens

But "O'Bri" finds nothing.

What do you think I'm doing wrong?

[Dave Watts]

I apologize in advance, I haven't read all of the older posts in the thread. But when you use CFQUERYPARAM, you're basically telling the database to separate code and data. Your wildcards should be data, but because they're not in the CFQUERYPARAM, they're going to be treated like code. So, you should be able to do something like this:

<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#Variables.SearchName#%">

Dave Watts, Eidolon LLC

[thanesherrington]

Hi Dave,

  Thanks for your suggestion.  Using:

WHERE LOWER(LastName) LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#Variables.SearchName#%">

Does the same thing as before.  Names without apostrophes work fine, names with fail.  I think the apostrophe isn't being escaped, but I can't figure it out.

[Dave Watts]

I've just read through more of the thread. I think this may be a problem with MS Access and how it's interpreting CFQUERYPARAM. This isn't a problem I've seen with other databases - and I haven't used MS Access in a long time.

Dave Watts, Eidolon LLC

[Charlie Arehart]

Dave makes a good point. And makes me think of something else to consider: I recall that Access uses double-quotes in where clause string values, where SQL Server uses single. So perhaps the issue is to change from ' to ", in either your or my examples where that was used.

Let us know how it goes.

[Charlie Arehart]

Woah: and I just found (in a doc page for use of the like oeprator in Access) that it does not list supporting the % (like SQL Server does), but instead just uses "*". Check for yourself: https://support.office.com/en-us/article/like-operator-b2f7ef03-9085-4ffb-9829-eef18358e931

Perhaps that's why some one of your variants tried so far was "close" but not quite right. If you do ultimately get it working, do reply here with the ultimate resolution, in case someone else someday finds it, while looking for the answer.