Skip to content

Commit fc9470d

Browse files
rldhontrldhont
authoredSep 30, 2016
Merge pull request #3535 from rldhont/expression_like_escape
[BUGFIX] Expression in like escape % and _
·
ltr-3_40final-3_0_0
2 parents c9d25b2 + f47a732 commit fc9470d

File tree

4 files changed

+60
-21
lines changed

4 files changed

+60
-21
lines changed
 

‎resources/function_help/json/ILIKE

Lines changed: 15 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -3,16 +3,22 @@
33
"type": "operator",
44
"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.",
55
"arguments": [
6-
{"arg":"string/number","description":"string to search"},
7-
{"arg":"pattern","description":"pattern to find"}
6+
{"arg":"string/number","description":"string to search"},
7+
{"arg":"pattern","description":"pattern to find, you can use '%' as a wildcard, '_' as a single char and '\\\\' to escape."}
88
],
99
"examples": [
10-
{ "expression":"'A' ILIKE 'A'", "returns":"1"},
11-
{ "expression":"'A' ILIKE 'a'", "returns":"1"},
12-
{ "expression":"'A' ILIKE 'B'", "returns":"0"},
13-
{ "expression":"'ABC' ILIKE 'b'", "returns":"0"},
14-
{ "expression":"'ABC' ILIKE 'B'", "returns":"0"},
15-
{ "expression":"'ABC' ILIKE '%b%'", "returns":"1"},
16-
{ "expression":"'ABC' ILIKE '%B%'", "returns":"1"}
10+
{ "expression":"'A' ILIKE 'A'", "returns":"1"},
11+
{ "expression":"'A' ILIKE 'a'", "returns":"1"},
12+
{ "expression":"'A' ILIKE 'B'", "returns":"0"},
13+
{ "expression":"'ABC' ILIKE 'b'", "returns":"0"},
14+
{ "expression":"'ABC' ILIKE 'B'", "returns":"0"},
15+
{ "expression":"'ABC' ILIKE '_b_'", "returns":"1"},
16+
{ "expression":"'ABC' ILIKE '_B_'", "returns":"1"},
17+
{ "expression":"'ABCD' ILIKE '_b_'", "returns":"0"},
18+
{ "expression":"'ABCD' ILIKE '_B_'", "returns":"0"},
19+
{ "expression":"'ABCD' ILIKE '_b%'", "returns":"1"},
20+
{ "expression":"'ABCD' ILIKE '_B%'", "returns":"1"},
21+
{ "expression":"'ABCD' ILIKE '%b%'", "returns":"1"},
22+
{ "expression":"'ABCD' ILIKE '%B%'", "returns":"1"}
1723
]
1824
}

‎resources/function_help/json/LIKE

Lines changed: 12 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3,14 +3,19 @@
33
"type": "operator",
44
"description": "Returns 1 if the first parameter matches the supplied pattern. Works with numbers also.",
55
"arguments": [
6-
{"arg":"string/number","description":"value"},
7-
{"arg":"pattern","description":"pattern to compare value with"}
6+
{"arg":"string/number","description":"value"},
7+
{"arg":"pattern","description":"pattern to compare value with, you can use '%' as a wildcard, '_' as a single char and '\\\\' to escape."}
88
],
99
"examples": [
10-
{ "expression":"'A' LIKE 'A'", "returns":"1"},
11-
{ "expression":"'A' LIKE 'a'", "returns":"0"},
12-
{ "expression":"'A' LIKE 'B'", "returns":"0"},
13-
{ "expression":"'ABC' LIKE 'B'", "returns":"0"},
14-
{ "expression":"'ABC' LIKE '%B%'", "returns":"1"}
10+
{ "expression":"'A' LIKE 'A'", "returns":"1"},
11+
{ "expression":"'A' LIKE 'a'", "returns":"0"},
12+
{ "expression":"'A' LIKE 'B'", "returns":"0"},
13+
{ "expression":"'ABC' LIKE 'B'", "returns":"0"},
14+
{ "expression":"'ABC' LIKE '_B_'", "returns":"1"},
15+
{ "expression":"'ABCD' LIKE '_B_'", "returns":"0"},
16+
{ "expression":"'ABCD' LIKE '_B%'", "returns":"1"},
17+
{ "expression":"'ABCD' LIKE '%B%'", "returns":"1"},
18+
{ "expression":"'1%' LIKE '1\\\\%'", "returns":"1"},
19+
{ "expression":"'1_' LIKE '1\\\\%'", "returns":"0"}
1520
]
1621
}

‎src/core/qgsexpression.cpp

Lines changed: 27 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -4318,9 +4318,33 @@ QVariant QgsExpression::NodeBinaryOperator::eval( QgsExpression *parent, const Q
43184318
if ( mOp == boLike || mOp == boILike || mOp == boNotLike || mOp == boNotILike ) // change from LIKE syntax to regexp
43194319
{
43204320
QString esc_regexp = QRegExp::escape( regexp );
4321-
// XXX escape % and _ ???
4322-
esc_regexp.replace( '%', ".*" );
4323-
esc_regexp.replace( '_', '.' );
4321+
// manage escape % and _
4322+
if ( esc_regexp.startsWith( '%' ) )
4323+
{
4324+
esc_regexp.replace( 0, 1, ".*" );
4325+
}
4326+
QRegExp rx( "[^\\\\](%)" );
4327+
int pos = 0;
4328+
while (( pos = rx.indexIn( esc_regexp, pos ) ) != -1 )
4329+
{
4330+
esc_regexp.replace( pos + 1, 1, ".*" );
4331+
pos += 1;
4332+
}
4333+
rx.setPattern( "\\\\%" );
4334+
esc_regexp.replace( rx, "%" );
4335+
if ( esc_regexp.startsWith( '_' ) )
4336+
{
4337+
esc_regexp.replace( 0, 1, "." );
4338+
}
4339+
rx.setPattern( "[^\\\\](_)" );
4340+
pos = 0;
4341+
while (( pos = rx.indexIn( esc_regexp, pos ) ) != -1 )
4342+
{
4343+
esc_regexp.replace( pos + 1, 1, '.' );
4344+
pos += 1;
4345+
}
4346+
rx.setPattern( "\\\\_" );
4347+
esc_regexp.replace( rx, "_" );
43244348
matches = QRegExp( esc_regexp, mOp == boLike || mOp == boNotLike ? Qt::CaseSensitive : Qt::CaseInsensitive ).exactMatch( str );
43254349
}
43264350
else

‎tests/src/core/testqgsexpression.cpp

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -490,9 +490,13 @@ class TestQgsExpression: public QObject
490490

491491
// regexp, like
492492
QTest::newRow( "like 1" ) << "'hello' like '%ll_'" << false << QVariant( 1 );
493-
QTest::newRow( "like 2" ) << "'hello' like 'lo'" << false << QVariant( 0 );
494-
QTest::newRow( "like 3" ) << "'hello' like '%LO'" << false << QVariant( 0 );
493+
QTest::newRow( "like 2" ) << "'hello' like '_el%'" << false << QVariant( 1 );
494+
QTest::newRow( "like 3" ) << "'hello' like 'lo'" << false << QVariant( 0 );
495+
QTest::newRow( "like 4" ) << "'hello' like '%LO'" << false << QVariant( 0 );
495496
QTest::newRow( "ilike" ) << "'hello' ilike '%LO'" << false << QVariant( 1 );
497+
// the \\\\ is like \\ in the interface
498+
QTest::newRow( "like escape 1" ) << "'1%' like '1\\\\%'" << false << QVariant( 1 );
499+
QTest::newRow( "like escape 2" ) << "'1_' like '1\\\\%'" << false << QVariant( 0 );
496500
QTest::newRow( "regexp 1" ) << "'hello' ~ 'll'" << false << QVariant( 1 );
497501
QTest::newRow( "regexp 2" ) << "'hello' ~ '^ll'" << false << QVariant( 0 );
498502
QTest::newRow( "regexp 3" ) << "'hello' ~ 'llo$'" << false << QVariant( 1 );

0 commit comments

Comments
 (0)
Please sign in to comment.