4

"GNR" is a string column and I expect the filter expression "GNR"='.11' to only return the feature with the value '0.11'.

Why are the other features with values of '.110'and '.1100' included?

Is there something wrong with my query?

string filter expression returns too many results

2
  • What if the column GNR was column of strings and that we only wanted to filter the strings that contained a certain sequence of characters? For example the string "(historical)" that ends certain strings in the list. So there is no perfect equal.
    – user53059
    Jun 2, 2015 at 11:38
  • 1
    Easy: "NAME"LIKE'%(historical)%'
    – user53059
    Jun 2, 2015 at 11:44

1 Answer 1

6

There is nothing wrong with your query.

= is a comparison operator and it returns all the records which begins with your string

Use this instead:

"GNR" LIKE '.11'

When you compare alphabetic characters, use ILIKE instead.


EDIT

In QGIS 2.0.1 or newer you can use regular expressions, also:

regexp_match("GNR", '^.11$')

where:

  • ^ indicates the start of the string;
  • $ indicates the end of the string.
5
  • Thanks. That did the trick. For me, coming from an SQL background, 'LIKE' and '=' act just the other way round. Jan 28, 2014 at 13:24
  • 1
    This is a bug then. = should be doing an exact match.
    – Nathan W
    Jan 28, 2014 at 23:31
  • Ok. This bug/implementation detail. QgsExpression will try and convert both sides to a double and compare that for faster results.
    – Nathan W
    Jan 28, 2014 at 23:33
  • 1
    You can also do this: "GNR" ~ '^.11$' which is a regex match and will force a string.
    – Nathan W
    Jan 28, 2014 at 23:34
  • Nathan did great observations! Therefore, due to the perfect compatibility with the older QGIS versions (tested on 1.8 and 1.7), "GNR" ~ '^.11$' is the way to go when you need equality! Jan 29, 2014 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.