Highlighted

Search queries with single quotes

New Here ,
Dec 07, 2019

Copy link to clipboard

Copied

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?

TOPICS
Database access

Views

177

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Search queries with single quotes

New Here ,
Dec 07, 2019

Copy link to clipboard

Copied

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?

TOPICS
Database access

Views

178

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Dec 07, 2019 0
Adobe Community Professional ,
Dec 07, 2019

Copy link to clipboard

Copied

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

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 07, 2019 0
New Here ,
Dec 08, 2019

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 08, 2019 0
Adobe Community Professional ,
Dec 08, 2019

Copy link to clipboard

Copied

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.

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 08, 2019 0
New Here ,
Dec 09, 2019

Copy link to clipboard

Copied

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?

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 09, 2019 0
Adobe Community Professional ,
Dec 09, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 09, 2019 0
New Here ,
Dec 09, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 09, 2019 0
Adobe Community Professional ,
Dec 09, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 09, 2019 0
Adobe Community Professional ,
Dec 09, 2019

Copy link to clipboard

Copied

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 (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 09, 2019 0
Adobe Community Professional ,
Dec 09, 2019

Copy link to clipboard

Copied

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.

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 09, 2019 0