On Oracle12 database, we have this table tbl_house_number that has one column "house_number" which is a varchar2 and has data entries of all different combinations.
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 are some sample house_numbers:
217 3RDFL, 2173RDFL, 500D, 527 # 2ND, 527# 2ND, 5422NDFL, 5422, 30# D2, 1250 2ND,
12502ND, 217 3RDFL, 2173RDFL, 5241R, 3 2R, 32R, 5092R, 24 # 2R, 24# 2R, 129 B-16,
129# B17, 129B-16, 16 # 2B, 4229B, 539# APT 3, 563 # A5,2162A, 934-A, 109-A, 511-A, 339-REA, 339REAR
And for the above house_numbers, the expected corresponding outputs should be as follows:
217, 217, 500, 527, 527, 542, 542, 30,1250,1250, 217, 217, 5241, 3, 32, 5092, 24,
24, 129, 129, 129, 16, 4229, 539, 563, 2162, 934, 109, 511, 339, 339
The best sql i can come up with is below, but it still does not resolve a situation when the data is 3933thfl....and the correct data should be 393...but my below sql code will show as 3933.
select house_number,
REGEXP_SUBSTR(replace(replace(replace(replace(replace(replace(house_number,'1/2',''),'-',''), '+', ''), '.', ''), '#', ''), '?', ''),'^\d+') AS "House_Number"
from tbl_HOUSE_NUMBER
where house_number != '0' and house_number != '00' and house_number != '000' and house_number != '0000' and house_number != '00000' and house_number != '000000'
order by 1;
Any help would be appreciated much.
Thanks.
Pro-tip: make sure you save your LiveSQL script before sharing the URL! Public URLs are in this format:
https://livesql.oracle.com/apex/livesql/file/content We can't access the URL provided.
Anyway, I don't understand why you say 3933thfl should be 393 and not 3933? 3 is a digit, right?
In any case, you can simplify the replace to:
1. Find the digits 0-9
2. Then match any character that isn't a comma
3. Finally match a comma or end-of-line ($)
You can then use backreferences to keep parts one and three:
with house_numbers as (
select '217 3RDFL, 2173RDFL, 500D, 527 # 2ND, 527# 2ND, 5422NDFL, 5422, 30# D2, 1250 2ND,
12502ND, 217 3RDFL, 2173RDFL, 5241R, 3 2R, 32R, 5092R, 24 # 2R, 24# 2R, 129 B-16,
129# B17, 129B-16, 16 # 2B, 4229B, 539# APT 3, 563 # A5,2162A, 934-A, 109-A, 511-A, 339-REA, 339REAR, 3933thfl' house_number from dual
)
select house_number,
regexp_replace(house_number, '([0-9]+)[^,]*(,|$)', '\1\2') AS "House_Number"
from house_numbers
where house_number != '0' and house_number != '00' and house_number != '000' and house_number != '0000' and house_number != '00000' and house_number != '000000'
order by 1;
HOUSE_NUMBER
---------------------------------------------------------------------------------------------------------------
House_Number
---------------------------------------------------------------------------------------------------------------
217 3RDFL, 2173RDFL, 500D, 527 # 2ND, 527# 2ND, 5422NDFL, 5422, 30# D2, 1250 2ND,
12502ND, 217 3RDFL, 2173RDFL, 5241R, 3 2R, 32R, 5092R, 24 # 2R, 24# 2R, 129 B-16,
129# B17, 129B-16, 16 # 2B, 4229B, 539# APT 3, 563 # A5,2162A, 934-A, 109-A, 511-A, 339-REA, 339REAR, 3933thfl
217, 2173, 500, 527, 527, 5422, 5422, 30, 1250,
12502, 217, 2173, 5241, 3, 32, 5092, 24, 24, 129,
129, 129, 16, 4229, 539, 563,2162, 934, 109, 511, 339, 339, 3933