Merge pull request #3535 from rldhont/expression_like_escape

[BUGFIX] Expression in like escape % and _
qgis · Sep 30, 2016 · fc9470d · fc9470d
2 parents c9d25b2 + f47a732
commit fc9470d
Show file tree

Hide file tree

Showing 4 changed files with 60 additions and 21 deletions.
diff --git a/resources/function_help/json/ILIKE b/resources/function_help/json/ILIKE
@@ -3,16 +3,22 @@
   "type": "operator",
   "description": "Returns 1 if the first parameter matches case-insensitive the supplied pattern. LIKE can be used instead of ILIKE to make the match case-sensitive. Works with numbers also.",
   "arguments": [
-	{"arg":"string/number","description":"string to search"},
-	{"arg":"pattern","description":"pattern to find"}
+    {"arg":"string/number","description":"string to search"},
+    {"arg":"pattern","description":"pattern to find, you can use '%' as a wildcard, '_' as a single char and '\\\\' to escape."}
    ],
   "examples": [
-	{ "expression":"'A' ILIKE 'A'", "returns":"1"},
-	{ "expression":"'A' ILIKE 'a'", "returns":"1"},
-	{ "expression":"'A' ILIKE 'B'", "returns":"0"},
-	{ "expression":"'ABC' ILIKE 'b'", "returns":"0"},
-	{ "expression":"'ABC' ILIKE 'B'", "returns":"0"},
-	{ "expression":"'ABC' ILIKE '%b%'", "returns":"1"},
-	{ "expression":"'ABC' ILIKE '%B%'", "returns":"1"}
+    { "expression":"'A' ILIKE 'A'", "returns":"1"},
+    { "expression":"'A' ILIKE 'a'", "returns":"1"},
+    { "expression":"'A' ILIKE 'B'", "returns":"0"},
+    { "expression":"'ABC' ILIKE 'b'", "returns":"0"},
+    { "expression":"'ABC' ILIKE 'B'", "returns":"0"},
+    { "expression":"'ABC' ILIKE '_b_'", "returns":"1"},
+    { "expression":"'ABC' ILIKE '_B_'", "returns":"1"},
+    { "expression":"'ABCD' ILIKE '_b_'", "returns":"0"},
+    { "expression":"'ABCD' ILIKE '_B_'", "returns":"0"},
+    { "expression":"'ABCD' ILIKE '_b%'", "returns":"1"},
+    { "expression":"'ABCD' ILIKE '_B%'", "returns":"1"},
+    { "expression":"'ABCD' ILIKE '%b%'", "returns":"1"},
+    { "expression":"'ABCD' ILIKE '%B%'", "returns":"1"}
   ]
 }
diff --git a/resources/function_help/json/LIKE b/resources/function_help/json/LIKE
@@ -3,14 +3,19 @@
   "type": "operator",
   "description": "Returns 1 if the first parameter matches the supplied pattern. Works with numbers also.",
   "arguments": [
-	{"arg":"string/number","description":"value"},
-	{"arg":"pattern","description":"pattern to compare value with"}
+    {"arg":"string/number","description":"value"},
+    {"arg":"pattern","description":"pattern to compare value with, you can use '%' as a wildcard, '_' as a single char and '\\\\' to escape."}
   ],
   "examples": [
-	{ "expression":"'A' LIKE 'A'", "returns":"1"},
-	{ "expression":"'A' LIKE 'a'", "returns":"0"},
-	{ "expression":"'A' LIKE 'B'", "returns":"0"},
-	{ "expression":"'ABC' LIKE 'B'", "returns":"0"},
-	{ "expression":"'ABC' LIKE '%B%'", "returns":"1"}
+    { "expression":"'A' LIKE 'A'", "returns":"1"},
+    { "expression":"'A' LIKE 'a'", "returns":"0"},
+    { "expression":"'A' LIKE 'B'", "returns":"0"},
+    { "expression":"'ABC' LIKE 'B'", "returns":"0"},
+    { "expression":"'ABC' LIKE '_B_'", "returns":"1"},
+    { "expression":"'ABCD' LIKE '_B_'", "returns":"0"},
+    { "expression":"'ABCD' LIKE '_B%'", "returns":"1"},
+    { "expression":"'ABCD' LIKE '%B%'", "returns":"1"},
+    { "expression":"'1%' LIKE '1\\\\%'", "returns":"1"},
+    { "expression":"'1_' LIKE '1\\\\%'", "returns":"0"}
   ]
 }
diff --git a/src/core/qgsexpression.cpp b/src/core/qgsexpression.cpp
@@ -4318,9 +4318,33 @@ QVariant QgsExpression::NodeBinaryOperator::eval( QgsExpression *parent, const Q
         if ( mOp == boLike || mOp == boILike || mOp == boNotLike || mOp == boNotILike ) // change from LIKE syntax to regexp
         {
           QString esc_regexp = QRegExp::escape( regexp );
-          // XXX escape % and _  ???
-          esc_regexp.replace( '%', ".*" );
-          esc_regexp.replace( '_', '.' );
+          // manage escape % and _
+          if ( esc_regexp.startsWith( '%' ) )
+          {
+            esc_regexp.replace( 0, 1, ".*" );
+          }
+          QRegExp rx( "[^\\\\](%)" );
+          int pos = 0;
+          while (( pos = rx.indexIn( esc_regexp, pos ) ) != -1 )
+          {
+            esc_regexp.replace( pos + 1, 1, ".*" );
+            pos += 1;
+          }
+          rx.setPattern( "\\\\%" );
+          esc_regexp.replace( rx, "%" );
+          if ( esc_regexp.startsWith( '_' ) )
+          {
+            esc_regexp.replace( 0, 1, "." );
+          }
+          rx.setPattern( "[^\\\\](_)" );
+          pos = 0;
+          while (( pos = rx.indexIn( esc_regexp, pos ) ) != -1 )
+          {
+            esc_regexp.replace( pos + 1, 1, '.' );
+            pos += 1;
+          }
+          rx.setPattern( "\\\\_" );
+          esc_regexp.replace( rx, "_" );
           matches = QRegExp( esc_regexp, mOp == boLike || mOp == boNotLike ? Qt::CaseSensitive : Qt::CaseInsensitive ).exactMatch( str );
         }
         else

diff --git a/tests/src/core/testqgsexpression.cpp b/tests/src/core/testqgsexpression.cpp
@@ -490,9 +490,13 @@ class TestQgsExpression: public QObject
 
       // regexp, like
       QTest::newRow( "like 1" ) << "'hello' like '%ll_'" << false << QVariant( 1 );
-      QTest::newRow( "like 2" ) << "'hello' like 'lo'" << false << QVariant( 0 );
-      QTest::newRow( "like 3" ) << "'hello' like '%LO'" << false << QVariant( 0 );
+      QTest::newRow( "like 2" ) << "'hello' like '_el%'" << false << QVariant( 1 );
+      QTest::newRow( "like 3" ) << "'hello' like 'lo'" << false << QVariant( 0 );
+      QTest::newRow( "like 4" ) << "'hello' like '%LO'" << false << QVariant( 0 );
       QTest::newRow( "ilike" ) << "'hello' ilike '%LO'" << false << QVariant( 1 );
+      // the \\\\ is like \\ in the interface
+      QTest::newRow( "like escape 1" ) << "'1%' like '1\\\\%'" << false << QVariant( 1 );
+      QTest::newRow( "like escape 2" ) << "'1_' like '1\\\\%'" << false << QVariant( 0 );
       QTest::newRow( "regexp 1" ) << "'hello' ~ 'll'" << false << QVariant( 1 );
       QTest::newRow( "regexp 2" ) << "'hello' ~ '^ll'" << false << QVariant( 0 );
       QTest::newRow( "regexp 3" ) << "'hello' ~ 'llo$'" << false << QVariant( 1 );