CF11 QoQ search for a square bracket character

Question

In a ColdFusion 11 application, I have a query object that contains strings that include an opening square bracket character - [. I need to be able to do a query of queries (QoQ) search of that query object to find any records that include the [ character, but I can't seem to find a way to escape it.

The best suggestion I've found - LIKE '%[[]%' - returns no results.

This code returns an error message:

<cfquery name="temp" dbType="query">
 SELECT *
 FROM myQuery
 WHERE myField LIKE '%[%'
</cfquery>

This code also returns an error message:

<cfquery name="temp" dbType="query">
 SELECT *
 FROM myQuery
 WHERE myField LIKE '%\[%' ESCAPE '\'
</cfquery>

And this code returns no records at all, even though I know the character is there:

<cfquery name="temp" dbType="query">
 SELECT *
 FROM myQuery
 WHERE myField LIKE '%[[]%'
</cfquery>

Any suggestions would be greatly appreciated. Thanks.

http://stackoverflow.com/a/21999934/3112803 – gfrobenius Jan 29 '16 at 16:06 — gfrobenius, Jan 29 '16 at 16:06

beloitdavisja · Accepted Answer · 2016-01-29T16:39:00.277

5

You were close. Using the link that gfrobenius posted, you get this:

<cfquery name="temp" dbType="query">
 SELECT *
 FROM myQuery
 WHERE myField LIKE '%[\[ ]%'
</cfquery>

edited Jan 29 '16 at 16:39

answered Jan 29 '16 at 16:04

beloitdavisja

1,509
10
13

Thanks beloitdavisja. I had seen the page that gfrobenius had posted. I probably should have taken a closer look at it. The code you posted did find my records with open brackets, but it also found records that do not contain an open bracket - It looks like it also finding records that contain a literal underscore character. So there is something still not quite right. – M.Shute Jan 29 '16 at 16:27
I successfully tested this answer, only with a query parameter. Also with query parameters, I got similar results using the code in the question. – Dan Bracuk Jan 29 '16 at 16:30
In my tests it looks like the code is also finding records that contain a literal underscore characters. Let me try ti with cfQueryParam and see if that helps. – M.Shute Jan 29 '16 at 16:32
I think I got it. Removing the underscore and replacing with a space somehow does it right...QoQ is so weird sometimes. I've updated the answer. – beloitdavisja Jan 29 '16 at 16:39
Yeah, even using cfQueryParam, I'm still getting records that contain underscores as well as open brackets. – M.Shute Jan 29 '16 at 16:40
Replacing the underscore with a space seems to have done it. I'm going to do a bit more testing, but this looks promising. Thanks! – M.Shute Jan 29 '16 at 16:41
Nevermind. Same problem just with spaces. This thing is frustrating. – beloitdavisja Jan 29 '16 at 16:42
Yes, while the space seems to have solved the original problem of finding ALL records that contain open brackets, I've discovered that I can't refine it by adding a few characters after the open bracket. `LIKE '%[\[ ]test%'` won't find any records that contain "[test" for example. Still, the original issue I posted about seems to be solved. – M.Shute Jan 29 '16 at 16:48
Square brackets have always been problematic with QoQ's, since they are reserved characters. That said, honestly I am leaning towards bug, rather than a well kept secret ;-) – Leigh Jan 29 '16 at 20:37

CF11 QoQ search for a square bracket character

1 Answers1