Query of Queries (QofQ) Escaped Character Problem

[Homestar9]

Hello All,

I'm trying to run a query or queires (QofQ) and I'm doing a LIKE comparison that looks for bracket characters ([ ]) within a string, however ColdFusion is ignoring the brackets. How can I escape the bracket character? So far I have only been able to escape the percent sign based on the ColdFusion Live Docs. The error message I get when I run the query below is:

Invalid Escape Sequence. Valid sequence pairs for this escape character are: "\%", or "\_".

Here is the query:
<cfquery dbtype="query" name="getLogs">
SELECT *
FROM GetLogs
WHERE Description LIKE '%\[User:#UserID#\]%' ESCAPE '\'
</cfquery>

Thanks for your help!

[cf_dev2]

Edit - Scratch that idea. It doesn't appear to work exactly as it should.

[Homestar9]

oops... yeah that didn't work.

You suggested using double brackets [[ ]].

I think the solution lies in using ESCAPE '\' but for some reason it doesn't like that combination of \ and [ ]

[cf_dev2]

I didn't test it extensively but it seemed like the matches weren't quite correct. For instance if #UserID# = 99 I think the query results included

Test record 1: [User:99] ....blah
Test record 2: [User:9999] ....blah
Test record 3: [User:999]....blah

But it should only have included record 1. Then again maybe my brain was tired 😉

[Homestar9]

You are correct. If you leave the brackets in the LIKE statement, it will return results as if the brackets weren't there at all.

Perhaps I need to figure out the ASCII character value of the bracket and include it that way i.e. #Char(?)# where the question mark would be the numerical value of that character.

My temporary solution has been to leave off the starting bracket:

<cfquery dbtype="query" name="getLogs">
SELECT *
FROM GetLogs
WHERE Description LIKE '%user:#UserID#]%'
</cfquery>

This has (so far) returned the results i'm looking for although its not as 100% accurate without that beginning [ in the LIKE statement.

[cf_dev2]

Edit - Duh. Yes square brackets [ ] are used to indicate a range of characters. That's why the results aren't quite what you'd expect.

http://livedocs.adobe.com/coldfusion/7/htmldocs/00001269.htm
"Square brackets ([ ]) represents any character in the range."

[cf_dev2 · Accepted solution]

What about this?

WHERE Description LIKE '%[[]User:#UserID#]%'

[Homestar9]

Thats the trick! The '[[]' method worked. Here is the final LIKE statement: Description LIKE '%[[]user:#UserID#]%'

Nice Job!