How can I perform a SQL SELECT with a LIKE condition for a string containing an open bracket character?

Question

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?

Answer 1

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.