5

I'm using QGIS and I am trying to filter a dataset using the advanced filter. My dataset contains earthquakes in the U.S., Canada and Mexico, however I would like to exclude Canada & Mexico for now.

The problem arises when I filter using: 

  "place" LIKE '%Mexico%'

as this now includes Mexico AND New Mexico.

I've been trying to experiment with SQL commands but I've had no luck so far.

EDIT: Added screenshot of part of my attribute table, the column labeled "place" is what I'm trying to filter by. Partial Attribute Table

"earthquakes_2005.csv" dataset

Improve this question
10
  • Post a screen shot of your attribute table.
    – whyzar
    Commented Dec 4, 2016 at 14:43
  • @whyzar posted it
    – nukenine
    Commented Dec 4, 2016 at 14:52
  • 2
    Wildcards are evil -- Only use them when you know you can control the outcome. The proper SQL syntax here is WHERE "place" NOT IN ('Canada', 'Mexico')
    – Vince
    Commented Dec 4, 2016 at 15:11
  • 2
    How about "place" LIKE '%Mexico%' AND NOT "place" LIKE '%New Mexico%'
    – Kazuhito
    Commented Dec 4, 2016 at 16:44
  • 1
    Please edit the question to specify the values you have in ASCII, so that they're legible on all devices. I recommend adding a "country" column, then encoding it, so yhat indexing is possible. Wildcard and NOT searches are very inefficient and compound search terms are even more so).
    – Vince
    Commented Dec 4, 2016 at 19:05

2 Answers 2

Reset to default
3

Another workaround is to create a new field (e.g. new_place) which can be used as a filter. This field will contain all the original place names but replaces "New Mexico" with another name (e.g. "New Méjico"). You can use an expression like:

regexp_replace( "place", 'New Mexico', 'New Méjico')

Now you can use an advanced filter on the new field to take out any rows containing "Canada" and "Mexico". "New Mexico" can still be seen in your original place field:

"new_place" not like ('%Canada') and "new_place" not like ('%Mexico')
Improve this answer
2

In addition to using

"place" LIKE '%Mexico%'

add this additional constraint

CHAR_LENGTH(“ColumnName“) > 6

to be

"place" = 'Mexico' AND CHAR_LENGTH(“ColumnName“) = 6

Here are some additional query statements to keep in mind as well.

Improve this answer
5
  • hmm getting this error: Parser Error: syntax error, unexpected '(', expecting $end Eval Error: No root node! Parsing failed?
    – nukenine
    Commented Dec 4, 2016 at 15:04
  • I've updated the syntax, also can you share the file if it doesn't execute?
    – whyzar
    Commented Dec 4, 2016 at 15:11
  • Still didn't execute, I've uploaded the dataset
    – nukenine
    Commented Dec 4, 2016 at 15:38
  • Isn't a simple "place" = 'Mexico' enough (i.e. without the wildcard %)? I don't think the char_length adds any more value.
    – Matthias Kuhn
    Commented Dec 4, 2016 at 16:07
  • Yea I suppose that works too
    – whyzar
    Commented Dec 4, 2016 at 16:41

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.