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?

score 8 · Answer 1 · edited Feb 27 '14 at 16:50

8

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.

edited Feb 27 '14 at 16:50

Eric Belair

10,574
13
75
116

answered Feb 24 '14 at 21:57

Peter Boughton

110,170
32
120
176

1

FYI, you can also specify the escape character using the [ESCAPE clause](http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html#WSc3ff6d0ea77859461172e0811cbec0e4fd-7fd2) – Leigh Feb 25 '14 at 01:47
This is not working... For instance, if I use the value "xxx[en", the query looks like: SELECT * FROM MyQoQ WHERE DESCRIPTION LIKE '%xxx[[]en%', and no records are returned, even though a query directly to the database (i.e. SELECT * FROM MyDBTable WHERE MyDBTable.DESCRIPTION LIKE '%xxx[en%' finds records. – Eric Belair Feb 27 '14 at 16:10
1

Not sure what's up - I specifically tested it and it worked, but I don't have that code to hand. Trying again now it works for `[%]` but not `[[]`. :/ – Peter Boughton Feb 27 '14 at 16:29
If it's coming from a database, is there a reason you're not just querying that directly? – Peter Boughton Feb 27 '14 at 16:29
Just tried the ESCAPE clause Leigh mentioned and that's giving the error _"Invalid Escape Sequence. Valid sequence pairs for this escape character are: /%, or /_."_ - which is incorrect since I used backslash not forward (and only backslash works). This is on CF 10,0,11,285437 in case it's version specific. – Peter Boughton Feb 27 '14 at 16:32
@PeterBoughton, yes, it works great for % and _, and I'm going to implement this ASAP. But I still can't get [ to work. – Eric Belair Feb 27 '14 at 16:36
@PeterBoughton, the reason I'm not querying the database directly is because I'm caching the data in the 'master' query, and re-using it for several different queries. – Eric Belair Feb 27 '14 at 16:37
Guys - Weird. I am relatively certain I have used QoQ + ESCAPE before. Though it was a while ago. I cannot test it now, but will double check it later. – Leigh Feb 27 '14 at 16:38
@Leigh, here's what I'm getting in CF 8: Query Of Queries syntax error. Encountered "ESCAPE. ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem. -------------------------------------------------------------------------------- SQL SELECT * FROM qSearchableMaterials WHERE 1 = 1 AND ( UCASEDESCRIPTION LIKE '%$[01%' ) ORDER BY RANK ESCAPE '$' – Eric Belair Feb 27 '14 at 16:43
Either the internal logic, or the documentation, is broken on this one, because it seems ESCAPE *only* works for escaping `%` and `_`. Nothing else. Use it on any other characters, like `[` and you get some variation of the errors above. Ugh.. – Leigh Feb 27 '14 at 20:01
Ok, definitely a bug in CF. My description is `wi[bb]le` and searching for `%[[]bb]%` (`[bb]`) works but just `%[[]bb%` (`[bb`) returns no results - CF must be doing something dumb with its QoQ SQL parsing. v10,0,11,285437 – Peter Boughton Feb 27 '14 at 21:00
@PeterBoughton it looks like there's no way around this one.... I'm just going to remove all open brackets from my search strings at this point. I'd rather have more search results come back than an exception. Thank you. – Eric Belair Feb 27 '14 at 21:04

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

1 Answers1

Linked