I have an attribute table with the fields "name" and "desc". In the "desc" field i have the following string: use:xxx population:xxx. The xxx are examples given that it is a very large dataset.

I want to extract the xxx after the population to another field called "population" using field calculator.

In the image bellow you can see the real format of the field "desc". My intention is to extract the number in float after the population (just the number). enter image description here

  • 1
    Please provide a few concrete examples of existing data to make tailored answers possible (using, right, left, regex, etc.) – Victor Oct 20 '16 at 10:06

Extract anything after population= to the end of line

regexp_substr("desc", 'population=(.*)\n')

if you want to do calculations, wrap it in a to_int(...)

A perfect match for today. Declaring it regexp day :)

|improve this answer|||||
  • Brilliant... good for me that today is regexp day ! CHEERS – Rodrigo Gonçalves Oct 20 '16 at 11:35

You can use the string manipulation functions for this, something (untested) like:

right(  "DESC",   length( "DESC") - strpos( "DESC", "population:") + length("population:")) 

to take all the characters to the RIGHT of the position of the string "population:" - you might want to check my maths on that.

|improve this answer|||||

Tested to work in QGIS 2.14. You can search for 'population:' which returns the begin index. You then take the right of the derived start index of the variable in question. The code is then casted in to_real() to get your float.

 to_real( right("desc", length("desc") - strpos("desc", 'population:') - length('population:') + 1)) 

I needed to ad +1 to the index.

Edit: if working with QGIS 2.8, use toreal() instead of to_real()

|improve this answer|||||

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.