4

ColdFusion query of queries syntax honors underscore as a single-character wildcard, and escaping of literal underscores with square brackets:

LIKE '_abc' -- any 1 char then abc
LIKE '[_]abc' -- actual underscore char then abc

Lucee (5.1 tested) doesn't appear to honor the wildcard, or the escaped version to match a literal underscore. Behavior is the same with or without cfqueryparam.

Does Lucee have a single character wildcard for QoQ, and if so, how do you escape that character when you want to find that actual literal?

enigment
  • 3,316
  • 7
  • 30
  • 35

1 Answers1

6

You can do this by escaping the underscore using the "at" character, e.g.

LIKE '@_@abc' escape '@'
andrewdixon
  • 1,059
  • 7
  • 16
  • Turns out that Lucee 5.1 crashes if you use NOT LIKE and ESCAPE together: Encountered "ESCAPE" at line [...]. Was expecting one of: "AND" ... "CONNECT" ... That's what my code does, so I didn't think ESCAPE was supported at all. Seems like a bug to me. – enigment Jan 20 '17 at 15:35
  • 1
    @enigment - Try the above, but put the NOT before the column name: ie `WHERE NOT Col LIKE '@_@abc' escape '@'` . [Seems to work](http://trycf.com/gist/69c450d1d18772853cb30e086ddabe5c/lucee5?theme=monokai). – Leigh Jan 20 '17 at 22:09
  • Well I'll be, you're right, that works, in both ACF and Lucee. Never knew that was legal SQL even. – enigment Jan 20 '17 at 23:57
  • @enigment - IIRC, I got it from SQL Server, which also [supports that syntax](https://msdn.microsoft.com/en-us/library/ms189455.aspx). Granted, I find `WHERE Col NOT LIKE ...` a bit more intuitive in SQL, but I guess it is not that different from using `!` for negation in CF, ie `if (!someCondition) {....`. – Leigh Jan 21 '17 at 13:49
  • I get the idea, didn't know it applied in that form to SQL. LSNED :) – enigment Jan 21 '17 at 15:52