CODEWITHSUNDEEP
sqlregexoracle-database
Aidax
Aidax

Reputation: 23

Matching All Numbers Up Until First Space

I've been scratching my head on this one. I tried the first result that I saw and many other iterations of it and I cannot figure it out.

You'll have to forgive the ugliness of my code, I just started SQL a week ago after being cast into the flames of 'get it done because no one else can'.

This is a snippet from my query. The field must contain 7 characters, spaces where it does not have data. I also am having to improvise because the database does not have a table for house numbers, but the house numbers are contained within the address fields. The thought process is, well just remove the numbers first. Or split at the first space. I'm going with regex.

RPAD(REGEXP_REPLACE("PATIENTS"."ADDRESS1",'[^0-9\s]+'),7) AS "HOUSENO",

Unfortunately this returns numbers beyond the first space. So addresses like 330 W 38th Street return as 

HOUSENO
------
33038

Any ideas how to only get "330" ?

I greatly appreciate your help.

Upvotes: 2

Views: 379

Answers (1)

GMB
GMB

Reputation: 222492

Instead of trying to remove unwanted parts of the string with regexp_replace(), you can capture the part that you want using regexp_substr():

rpad(regexp_substr(patients1.address1, '^\d+'), 7)

'^\d+' means: as many digits as possible at the beginning of the string (at least one digit).

Upvotes: 2

Related Questions