4

I want to print only the number part of the address field. I tried to use the "field calculator" with "regexp_substr" to extract just the number (only string before the first space) but couldn't figure out the right expression. I am also thinking that it could be done with labels, by showing only the first word of the address.

So "1250 Main Street" should only show "1250".

6

If the addresses are all formatted similarly as in your example, you may try this expression:

left( "1250 Main Street", strpos( "1250 Main Street" ,'\\s') - 1)

Firstly, the expression strpos finds the position of the first white space in the string (i.e. 5, the result is "1250 "); then it saves the left part of the string from the position 4: the result is 1250.

You may obviously change "1250 Main Street" with the name of the field of interest.

  • and (just in case you wonder if) you can easily use that expression directly on labels – ThingumaBob May 21 '17 at 20:17
  • 2
    cool, I had no idea you could use regular expressions in the strpos pattern. have an upvote! e.g. find first 4 digit number strpos( 'foo 123 4444','\\d{4}') – Steven Kay May 21 '17 at 20:39
0

Field Calculator > Create new field Output field name "NEWNAME"

and then select the column that has the address with the name and the number that you want to create a new column with just the number. in this example my column is "name"

left( "name", strpos( "name" ,'\s') - 1)

you should find a new column appear with the just the numbers pop up

  • Aside from the fact that this answer duplicates the expression suggested in the accepted answer, it is also inaccurate as backslash characters in regular expression strings need to be doubled to escape them ie: '\s' -> '\\s' – Andy Harfoot Aug 9 '18 at 10:47

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.