How can I perform a SQL SELECT with a LIKE condition for a string containing an open bracket character?
cfquery, coldfusion, qoq, sql
Solution
QoQ shares a feature of TSQL (MS SQL Server) whereby it's not just `%` and `_` that are wildcards in LIKE - it also supports regex-style character classes, as in`[a-z]` for any lowercase letter.
To escape these values and match the literal equivalents, you can use a character class itself, i.e. `[[]` will match a literal `[`, and of course you probably also want to escape any `%` and `_` in the user input - you can do all three like so:
'%#Url.SearchString.replaceAll('[\[%_]','[$0]')#%'
That is just a simple regex replace (using String.replaceAll) to match all instances of `[` or `%` or `_` and wrap each one in `[`..`]` - the `$0` on the replacement side represents the matched text.
Problem
I have a simple search query: ``` <cfquery name="_qSearch" dbtype="Query"> SELECT * FROM MyQoQ WHERE DESCRIPTION LIKE '%#URL.searchString#%' </cfquery> ``` This query works excellently for most values. However, if someone searches for a value like `"xxx[en"`, it bombs with the error message `The pattern of the LIKE conditional is malformed.`. Is there any way around this, since the bracket has a special use in CFQUERY?