Extracting number from address string using QGIS

Question

I have a table with addresses and I need to extract only the number from the attributes.

The format is as follows:

address
VALLEY VIEW HOUSE, 36 HIGH VIEW, USHAW MOOR, DH7 7QQ
35 HIGH VIEW, USHAW MOOR, DURHAM, DH7 7QQ
DURHAM ROAD, USHAW MOOR, DURHAM, DH7 7LF

If I use regexp_substr("address", '(\\d+)') it will correctly extract the number from the first 2 cases but on the 3rd one it will extract the number from the postcode BUT I do not want that to be extracted.

Is there a way to avoid that?

Answer 1

You have to add regex word boundaries (\b) to extract your numbers:

regexp_substr("address", '\\b\\d+\\b')

Answer 2

Use this expression:

regexp_substr(
    replace( 
        "address",
        regexp_substr("address",'\\d\\s\\d') ,
        ''
    ),
    '(\\d+)'
)