Home » SQL & PL/SQL » SQL & PL/SQL » extract numbers from a varchar2 upto non-numeric (12.1.0.2.0)
extract numbers from a varchar2 upto non-numeric [message #665749] Tue, 19 September 2017 15:07 Go to next message
philipebe
Messages: 19
Registered: September 2017
Junior Member
Hi,
We have a varchar2 column called House_Number, which has all different kind of data entries as shown below.
And all i need is the numbers from left to the first occurrence of a non-numeric like space, special characters and ascii characters.
Below is the original house_number, and the extracted value that we want.

House_Number extracted value that we want
---------------- -------------------------------
217 3RDFL 217
2173RDFL 2173 (would be great if we can get only 217)
500D 500
527 # 2ND 527
527# 2ND 527
5422NDFL 5422 (would be great if we can get only 542)
30# D2 30
1250 2ND 1250
12502ND 12502 (would be great if we can get only 1250)
217 3RDFL 217
2173RDFL 2173 (would be great if we can get only 217)
5241R 5241
3 2R 3
32R 32
5092R 5092
24 # 2R 24
24# 2R 24
129 B-16 129
129# B17 129
129B-16 129
16 # 2B 16
4229B 4229
539# APT 3 539
563 # A5 563
2162A 2162
934-A 934
109-A 109
511-A 511
339-REA 339
339REAR 339

I tried using regexp_substr, but i could not get a logic which will work for all the different data inputs.

Would really appreciate if someone can help.

Thanks.
Philip.
Re: extract numbers from a varchar2 upto non-numeric [message #665750 is a reply to message #665749] Tue, 19 September 2017 15:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
REGEXP_SUBSTR(House_Number,'^\d+')

SY.
Re: extract numbers from a varchar2 upto non-numeric [message #665751 is a reply to message #665750] Tue, 19 September 2017 15:58 Go to previous message
philipebe
Messages: 19
Registered: September 2017
Junior Member
Solomon,
Yup...that fixed it....simple solution....yet thats what we need for now.
Thanks much
Previous Topic: split name
Next Topic: compare 2 collections data
Goto Forum:
  


Current Time: Thu Mar 28 14:45:23 CDT 2024