6

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?

1
  • 1
    Correct, the postcode format is 2characters+1number+whitespace+1number+2characters.
    – StrAvoN
    Aug 24, 2021 at 14:21

2 Answers 2

8

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

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

Use this expression:

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

enter image description here

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.