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

  • Post a screen shot of your attribute table.
    – whyzar
    Dec 4 '16 at 14:43
  • @whyzar posted it
    – ron
    Dec 4 '16 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
    Dec 4 '16 at 15:11
  • 2
    How about "place" LIKE '%Mexico%' AND NOT "place" LIKE '%New Mexico%'
    – Kazuhito
    Dec 4 '16 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
    Dec 4 '16 at 19:05

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')

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.

  • hmm getting this error: Parser Error: syntax error, unexpected '(', expecting $end Eval Error: No root node! Parsing failed?
    – ron
    Dec 4 '16 at 15:04
  • I've updated the syntax, also can you share the file if it doesn't execute?
    – whyzar
    Dec 4 '16 at 15:11
  • Still didn't execute, I've uploaded the dataset
    – ron
    Dec 4 '16 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. Dec 4 '16 at 16:07
  • Yea I suppose that works too
    – whyzar
    Dec 4 '16 at 16:41

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.