Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Adobe QoQ supports MSSQL [] regex char sets in LIKE operator

Description

This SQL returns 1 matching row in MSSQL and Adobe CF QoQ:

employees = queryNew( 'name,foo', 'varchar,varchar',[ ['Brad','cm_4test5'], ['Luis','yeah'] ]); actual = QueryExecute( sql = "SELECT * from employees WHERE foo LIKE 'cm_[0-9]%[0-9]'", options = { dbtype: 'query' } ); writedump( actual );

Note, MySQL, Oracle, and Postgres do not support square bracket char sets in their LIKE operator.

This has potential backwards compat issues, so we need to also fix the escape character which currently does not work. The default escape char should be "\" and we should also support this syntax which is standard SQL:

WHERE col1 LIKE 'foo$%bar' ESCAPE '$'

Additional information. The square bracket char set works the same as regex. It matches a single character in the set or range of chars defined. Ex:

  • [abc] – Match a single char "a" or "b" or "c"

  • [0-9] – Match a single digit "0" through "9"

  •  

Failed to load

  • – match a single char NOT "x", "y", or "z"

Any existing code matching a literal square bracket such as

WHERE col1 LIKE 'foo[bar]baz%'

would need to update their code to the following to continue working after this change

WHERE col1 LIKE 'foo\[bar]baz%'

or-- if a custom escape char is preferred...

WHERE col1 LIKE 'foo@[bar]baz%' ESCAPE '@'

Activity

Show:

Pothys - MitrahSoft 16 May 2024 at 09:47

I've checked this ticket with Lucee version 6.1.0.142-SNAPSHOT. Now, Lucee QoQ supports [] regex charsets in the LIKE operator, and it works fine.

Michael Offner 16 May 2024 at 08:33

i manually adapted the PR, was the esiest way to do it, please give this a test

https://github.com/lucee/Lucee/commit/6e4ee879ed214484ddaf55f1938c7ede82e7a48c

Pothys - MitrahSoft 8 May 2023 at 15:04

Michael Offner 8 May 2023 at 14:05

can you please adapt this PR https://github.com/lucee/Lucee/pull/1546 for branch 6.0

Brad Wood 30 November 2022 at 04:49

Will you add this to 5.4? It’s probably already going to have conflicts with my new QoQ performance ticket which also touches the LIKE code.

Fixed

Details

Assignee

Reporter

Labels

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Sprint

Fix versions

Priority

Created 19 January 2022 at 19:02
Updated 18 September 2024 at 10:06
Resolved 16 May 2024 at 09:47

Flag notifications