extract numbers from a varchar2 upto non-numeric [message #665749] |
Tue, 19 September 2017 15:07 |
|
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.
|
|
|
|
|