7

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".

Improve this question

2 Answers 2

Reset to default
9

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.

Improve this answer
2
  • and (just in case you wonder if) you can easily use that expression directly on labels
    – geozelot
    Commented May 21, 2017 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
    Commented May 21, 2017 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

Improve this answer
1
  • 1
    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
    Commented Aug 9, 2018 at 10:47

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.