2

I wish to select records based on the NRN attribute in my dataset : "NRN" IN ('1','2','23','31')

Some records have multiple values in the NRN field separated by the underscore character.
For example : 20_21, 14_23 etc

How do I select any records that contain the values that I want ignoring the records that have part of the string but not the whole number? Example : select 14_23 but not 20_21.

Improve this question

2 Answers 2

Reset to default
2

You could use regular expressions to do this:

 regexp_match( "NRN",'(\\b|_)(1|2|23|31)(\\b|_)')

This will match either a "1", "2", "23" or "31" which is surrounded by "word boundaries" ( \b") or underscores. (A word boundary is either the start or end of the string, or certain whitespace characters). New numbers to match can be inserted into the "(1|2|23|31)" part - just seperate them with pipe characters.

Edit: if the seperator is a dash, then things are a bit easier (the reason is that dash is considered a word boundary by regular expressions):

regexp_match( "NRN",'\\b(1|2|23|31)\\b')

Edit: if you're using the expression as a filter, the regexp_match function won't be available. You can use a variation on Anita's answer:

 '-' || "NRN" || '-' like '%-21-%'
Improve this answer
8
  • An error occurred when executing the query. The data provider said: OGR[3] error 1: Undefined function 'regexp_match' used. QGIS version 2.4.0-Chugiak
    – flywire
    Commented Aug 25, 2014 at 9:10
  • Ahh.. That expression wont work in a filter window (since they use the providers native query language, not QGIS' inbuilt one). This expression will work however in the "select by expression" dialog. If you need a filter, use Anita's answer. You may even be able to use '' || "NRN" || '' like '%_21_%' to avoid having to modify your data (not sure if that will work with shapefiles/ogr or not)
    – ndawson
    Commented Aug 25, 2014 at 9:15
  • I don't want to change the data set. I am in Layer Properties General Query Builder An error occurred when executing the query. The data provider said: OGR[3] error 1: SQL Expression Parsing Error: syntax error, unexpected $undefined. Occured around : SELECT * FROM "aus10vgd_l" WHERE '' || "NRN" || '' like '%_21_%' - The error caret is under the layer name. BTW Quick response.
    – flywire
    Commented Aug 25, 2014 at 9:30
  • Sorry, I am in the "select by expression" dialog now. The separator is a minus not an underscore. I am still having issues with the separator.
    – flywire
    Commented Aug 25, 2014 at 9:39
  • Formatting of my reply got messed up. I've modified my answer with updated code.
    – ndawson
    Commented Aug 25, 2014 at 9:46
0

If you change the NRN field from 20_21 to _20_21_ then you can use "NRN" LIKE '%_21_%' to find all features which contain the number 21.

Improve this answer

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.