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 '15 at 11:38
  • 1
    Easy: "NAME"LIKE'%(historical)%'
    – user53059
    Jun 2 '15 at 11:44
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 '14 at 13:24
  • 1
    This is a bug then. = should be doing an exact match.
    – Nathan W
    Jan 28 '14 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 '14 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 '14 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 '14 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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