Search queries with single quotes

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.