Wildcard support in query of queries

dev support
1

Hello Im trying to establish if the sql wildcards supported in cold fusion within query of queries (using ‘like’ operator) are also supported (in some other way with different syntax) in Lucee. In particular, I can’t seem to get Lucee to respect this statement SELECT name FROM querya WHERE name LIKE ‘[a-z]%’;

these don’t seem to be supported by Lucee either

SELECT name FROM querya WHERE name LIKE 'A[^c]%'; 
SELECT name FROM querya WHERE name LIKE '%[]'; 
SELECT name FROM querya WHERE name LIKE 'A[%]%'; 
SELECT name FROM querya WHERE name LIKE 'A[^c-f]%';

CF documentation from v9 below:

  • The underscore (_) represents any single character.
  • The percent sign (%) represents zero or more characters.
  • Brackets [ ] represents any character in the range.
  • Brackets with a caret [^] represent any character not in the range.
  • All other characters represent themselves.
2

As far as I know the internals for QoQ use H2, which means the wildcards _ and % are supported at the least. You can find the H2 grammar here:

http://www.h2database.com/html/grammar.html

There also seems to be a REGEXP keyword (which I didn’t previously know about until I read the above docs), that might let you do some more advanced matching, but your code might end up being Lucee specific :slight_smile:

1 Like