Skip to Main Content
  • Questions
  • Need Regular expression to separate the addresses

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Murthy.

Asked: February 18, 2021 - 1:46 pm UTC

Last updated: February 18, 2021 - 5:25 pm UTC

Version: 12c

Viewed 100+ times

You Asked

Hi Team,
I need a generic regular expression that fits for all the combinations of addresses to separate the addresses into address1,address2 for all countries.
here is the sample data.
Thanks for your help.

with t1 as
(
select country,address from
(
select 'AE'country, 'Tawazun Industrial Park' address from dual union all
select 'AT'country, 'B&R Strasse 1' address from dual union all
select 'AT'country, 'Dr.-Auner-Straße 21' address from dual union all
select 'AU'country, '9 Help Street, Level 4' address from dual union all
select 'BA'country, 'ul. Trg nezavisnosti 5' address from dual union all
select 'BA'country, 'Poljice bb' address from dual union all
select 'BE'country, 'Bargiestraat 2' address from dual union all
select 'BE'country, 'PRESIDENT KENNEDYPARK 35' address from dual union all
select 'BG'country, 'Spectrum Building,Bequerel Avene' address from dual union all
select 'BG'country, 'bul.Tsarigradsko shose 90,' address from dual union all
select 'BM'country, 'Clarendon House 2 Church Street' address from dual union all
select 'BM'country, '22 Victoria Street' address from dual union all
select 'BR'country, 'Rua General Bertoldo Klinger, 277' address from dual union all
select 'BR'country, 'AV. ORLANDA BERGAMO 1062' address from dual union all
select 'CA'country, '4281 Harvester Rd.' address from dual union all
select 'CA'country, '1495 Franklin Street' address from dual union all
select 'CH'country, 'Helkenstr 13' address from dual union all
select 'CH'country, 'Tschachenstrasse' address from dual union all
select 'CN'country, 'No.555 XingShun Road' address from dual union all
select 'CN'country, '1 Aqi Road, Mapo Town' address from dual union all
select 'CZ'country, 'V Parku 2325/16' address from dual union all
select 'CZ'country, 'K Vidouli 15 / Safrankova 1' address from dual union all
select 'DE'country, 'Landshuter Str. 100' address from dual union all
select 'DE'country, 'Blochstr. 1' address from dual union all
select 'DK'country, 'Bang og Olufsen Allé 1' address from dual union all
select 'DK'country, 'Peter Bangsvej 15' address from dual union all
select 'DZ'country, '06 Boulevard Mohamed V' address from dual union all
select 'EE'country, 'Valga mnt 7a' address from dual union all
select 'EE'country, 'Ehitajate tee 5' address from dual union all
select 'EG'country, 'Public Free Zone, Nasr City' address from dual union all
select 'ES'country, 'Avenida John Lennon, s/n' address from dual union all
select 'ES'country, 'Poligono Malpica, C/F Oeste' address from dual union all
select 'FI'country, 'Karakaari 7' address from dual union all
select 'FI'country, 'Kutomotie 16' address from dual union all
select 'FR'country, '21 Av Du Marechal Foch' address from dual union all
select 'FR'country, '45 Rue De Villiers' address from dual union all
select 'GB'country, 'Woodhouse Lane' address from dual union all
select 'GB'country, '51 Jardine Crescent' address from dual union all
select 'GG'country, 'Third floor, Royal Bank Place' address from dual union all
select 'GG'country, 'Third floor, Royal Bank Place' address from dual union all
select 'HR'country, 'Štoosova 1' address from dual union all
select 'HR'country, 'Štoosova 1' address from dual union all
select 'HU'country, 'Harrer Pál u. 3-5.' address from dual union all
select 'HU'country, 'Damjanich János út 8.' address from dual union all
select 'IE'country, 'East Park' address from dual union all
select 'IE'country, 'Building 6550, Avenue 6000' address from dual union all
select 'IL'country, '30 Hasivim St.' address from dual union all
select 'IL'country, '24 Zarhin Street, POB 4334' address from dual union all
select 'IN'country, '#2064, SIRI IRIS – 2nd Floor' address from dual union all
select 'IN'country, 'Doddakannelli, Sarjapur Rd.' address from dual union all
select 'IS'country, 'Skulgata 19' address from dual union all
select 'IS'country, 'Skúlagötu 19' address from dual union all
select 'IT'country, 'Via Remo De Feo' address from dual union all
select 'IT'country, 'Via Statale 113' address from dual union all
select 'JP'country, 'Tokyo SumitomoTwin Building 27-1' address from dual union all
select 'JP'country, '4-16-1 Okada' address from dual union all
select 'KR'country, '150-20, Gongse-ro' address from dual union all
select 'KR'country, '199, Chongchon-Dong, Bupyong-Gu' address from dual union all
select 'KY'country, 'The Grand Pavillion, West Bay Road' address from dual union all
select 'KY'country, 'The Grand Pavilion,West Bay Road,' address from dual union all
select 'LI'country, 'Im alten Riet 102' address from dual union all
select 'LI'country, 'Im alten Riet 102' address from dual union all
select 'LU'country, '6D, Route de Treves' address from dual union all
select 'LU'country, '6D, Route de Trèves' address from dual union all
select 'LV'country, 'Akmeņu iela 72' address from dual union all
select 'LV'country, 'Ventspils Augsto tehnoloìiju parks' address from dual union all
select 'MA'country, 'Batiment B4, 1st Floor, Technopolis' address from dual union all
select 'MA'country, 'Lotissement El Menzeh' address from dual union all
select 'ME'country, 'Elektrotechnicki fakultet,' address from dual union all
select 'ME'country, 'Elektrotechnicki fakultet,' address from dual union all
select 'MX'country, 'Av. Hermanos Escobar 5756' address from dual union all
select 'MX'country, 'Ave. Hermanos Escobar 5756' address from dual union all
select 'MY'country, '737-1-10, Kompleks Sri Sg Nibong' address from dual union all
select 'MY'country, 'PLO 208 Jalan Cyber 14' address from dual union all
select 'NA'country, '2 Jakaranda St' address from dual union all
select 'NL'country, 'Science Park Eindhoven 5010' address from dual union all
select 'NL'country, 'Het Zuiderkruis 53' address from dual union all
select 'NO'country, 'Blindern' address from dual union all
select 'NO'country, 'Drengsrudhagen 2' address from dual union all
select 'PL'country, 'ul. Nowowiejska 15/19' address from dual union all
select 'PL'country, 'Al. Jana Kasprowicza 20' address from dual union all
select 'PT'country, 'Edif, Uninova, Suite 28' address from dual union all
select 'PT'country, 'Rua Max Grundig 35' address from dual union all
select 'RO'country, 'Nojoridului 90' address from dual union all
select 'RO'country, 'Strada Thomas Masaryk 19' address from dual union all
select 'RU'country, 'cheusescu street 5' address from dual union all
select 'RU'country, '5-4/5 Plotnikov per' address from dual union all
select 'SA'country, 'King Faisal Street AlKhumra' address from dual union all
select 'SA'country, '(* Need Street Address *)' address from dual union all
select 'SE'country, 'Fabriksgatan 13' address from dual union all
select 'SE'country, 'Chalmersplatsen 4' address from dual union all
select 'SG'country, '61, Science Park Rd.' address from dual union all
select 'SG'country, '61, Science park road' address from dual union all
select 'SI'country, 'Reboljeva ulica 1' address from dual union all
select 'SI'country, 'Partizanska 12' address from dual union all
select 'SK'country, 'Vazovova 5' address from dual union all
select 'SK'country, 'Domaniža 380' address from dual union all
select 'TR'country, 'Haluk Turksoy Sok' address from dual union all
select 'TR'country, 'Organise Sanayi Bolgesi 2' address from dual union all
select 'TW'country, '5F-6,No. 51, Keelung Rd., Sec.2,' address from dual union all
select 'TW'country, 'Ciyun Road' address from dual union all
select 'UA'country, 'Kulparkivska, 222A' address from dual union all
select 'UA'country, 'Kulparkivska 222A' address from dual union all
select 'US'country, '8000 W FLORISSANT AVE' address from dual union all
select 'US'country, '8600 MEMORIAL PKWY SW' address from dual union all
select 'ZA'country, 'Private Bag 3, WITS' address from dual union all
select 'ZA'country, 'Cnr Alpha and Branch Roads,Driehoek' address from dual
)
)
select country,address from t1;

and we said...

And how exactly are you defining the split between address 1 and 2? The location of the comma?

If so you can use something like this:

set define off
with t1 as
(
select country,address from
(
select 'AE'country, 'Tawazun Industrial Park' address from dual union all
select 'AT'country, 'B&R Strasse 1' address from dual union all
select 'AT'country, 'Dr.-Auner-Straße 21' address from dual union all
select 'AU'country, '9 Help Street, Level 4' address from dual union all
select 'BA'country, 'ul. Trg nezavisnosti 5' address from dual union all
select 'BA'country, 'Poljice bb' address from dual union all
select 'BE'country, 'Bargiestraat 2' address from dual union all
select 'BE'country, 'PRESIDENT KENNEDYPARK 35' address from dual union all
select 'BG'country, 'Spectrum Building,Bequerel Avene' address from dual union all
select 'BG'country, 'bul.Tsarigradsko shose 90,' address from dual union all
select 'BM'country, 'Clarendon House 2 Church Street' address from dual union all
select 'BM'country, '22 Victoria Street' address from dual union all
select 'BR'country, 'Rua General Bertoldo Klinger, 277' address from dual union all
select 'BR'country, 'AV. ORLANDA BERGAMO 1062' address from dual union all
select 'CA'country, '4281 Harvester Rd.' address from dual union all
select 'CA'country, '1495 Franklin Street' address from dual union all
select 'CH'country, 'Helkenstr 13' address from dual union all
select 'CH'country, 'Tschachenstrasse' address from dual union all
select 'CN'country, 'No.555 XingShun Road' address from dual union all
select 'CN'country, '1 Aqi Road, Mapo Town' address from dual union all
select 'CZ'country, 'V Parku 2325/16' address from dual union all
select 'CZ'country, 'K Vidouli 15 / Safrankova 1' address from dual union all
select 'DE'country, 'Landshuter Str. 100' address from dual union all
select 'DE'country, 'Blochstr. 1' address from dual union all
select 'DK'country, 'Bang og Olufsen Allé 1' address from dual union all
select 'DK'country, 'Peter Bangsvej 15' address from dual union all
select 'DZ'country, '06 Boulevard Mohamed V' address from dual union all
select 'EE'country, 'Valga mnt 7a' address from dual union all
select 'EE'country, 'Ehitajate tee 5' address from dual union all
select 'EG'country, 'Public Free Zone, Nasr City' address from dual union all
select 'ES'country, 'Avenida John Lennon, s/n' address from dual union all
select 'ES'country, 'Poligono Malpica, C/F Oeste' address from dual union all
select 'FI'country, 'Karakaari 7' address from dual union all
select 'FI'country, 'Kutomotie 16' address from dual union all
select 'FR'country, '21 Av Du Marechal Foch' address from dual union all
select 'FR'country, '45 Rue De Villiers' address from dual union all
select 'GB'country, 'Woodhouse Lane' address from dual union all
select 'GB'country, '51 Jardine Crescent' address from dual union all
select 'GG'country, 'Third floor, Royal Bank Place' address from dual union all
select 'GG'country, 'Third floor, Royal Bank Place' address from dual union all
select 'HR'country, 'Štoosova 1' address from dual union all
select 'HR'country, 'Štoosova 1' address from dual union all
select 'HU'country, 'Harrer Pál u. 3-5.' address from dual union all
select 'HU'country, 'Damjanich János út 8.' address from dual union all
select 'IE'country, 'East Park' address from dual union all
select 'IE'country, 'Building 6550, Avenue 6000' address from dual union all
select 'IL'country, '30 Hasivim St.' address from dual union all
select 'IL'country, '24 Zarhin Street, POB 4334' address from dual union all
select 'IN'country, '#2064, SIRI IRIS – 2nd Floor' address from dual union all
select 'IN'country, 'Doddakannelli, Sarjapur Rd.' address from dual union all
select 'IS'country, 'Skulgata 19' address from dual union all
select 'IS'country, 'Skúlagötu 19' address from dual union all
select 'IT'country, 'Via Remo De Feo' address from dual union all
select 'IT'country, 'Via Statale 113' address from dual union all
select 'JP'country, 'Tokyo SumitomoTwin Building 27-1' address from dual union all
select 'JP'country, '4-16-1 Okada' address from dual union all
select 'KR'country, '150-20, Gongse-ro' address from dual union all
select 'KR'country, '199, Chongchon-Dong, Bupyong-Gu' address from dual union all
select 'KY'country, 'The Grand Pavillion, West Bay Road' address from dual union all
select 'KY'country, 'The Grand Pavilion,West Bay Road,' address from dual union all
select 'LI'country, 'Im alten Riet 102' address from dual union all
select 'LI'country, 'Im alten Riet 102' address from dual union all
select 'LU'country, '6D, Route de Treves' address from dual union all
select 'LU'country, '6D, Route de Trèves' address from dual union all
select 'LV'country, 'Akmeņu iela 72' address from dual union all
select 'LV'country, 'Ventspils Augsto tehnoloìiju parks' address from dual union all
select 'MA'country, 'Batiment B4, 1st Floor, Technopolis' address from dual union all
select 'MA'country, 'Lotissement El Menzeh' address from dual union all
select 'ME'country, 'Elektrotechnicki fakultet,' address from dual union all
select 'ME'country, 'Elektrotechnicki fakultet,' address from dual union all
select 'MX'country, 'Av. Hermanos Escobar 5756' address from dual union all
select 'MX'country, 'Ave. Hermanos Escobar 5756' address from dual union all
select 'MY'country, '737-1-10, Kompleks Sri Sg Nibong' address from dual union all
select 'MY'country, 'PLO 208 Jalan Cyber 14' address from dual union all
select 'NA'country, '2 Jakaranda St' address from dual union all
select 'NL'country, 'Science Park Eindhoven 5010' address from dual union all
select 'NL'country, 'Het Zuiderkruis 53' address from dual union all
select 'NO'country, 'Blindern' address from dual union all
select 'NO'country, 'Drengsrudhagen 2' address from dual union all
select 'PL'country, 'ul. Nowowiejska 15/19' address from dual union all
select 'PL'country, 'Al. Jana Kasprowicza 20' address from dual union all
select 'PT'country, 'Edif, Uninova, Suite 28' address from dual union all
select 'PT'country, 'Rua Max Grundig 35' address from dual union all
select 'RO'country, 'Nojoridului 90' address from dual union all
select 'RO'country, 'Strada Thomas Masaryk 19' address from dual union all
select 'RU'country, 'cheusescu street 5' address from dual union all
select 'RU'country, '5-4/5 Plotnikov per' address from dual union all
select 'SA'country, 'King Faisal Street AlKhumra' address from dual union all
select 'SA'country, '(* Need Street Address *)' address from dual union all
select 'SE'country, 'Fabriksgatan 13' address from dual union all
select 'SE'country, 'Chalmersplatsen 4' address from dual union all
select 'SG'country, '61, Science Park Rd.' address from dual union all
select 'SG'country, '61, Science park road' address from dual union all
select 'SI'country, 'Reboljeva ulica 1' address from dual union all
select 'SI'country, 'Partizanska 12' address from dual union all
select 'SK'country, 'Vazovova 5' address from dual union all
select 'SK'country, 'Domaniža 380' address from dual union all
select 'TR'country, 'Haluk Turksoy Sok' address from dual union all
select 'TR'country, 'Organise Sanayi Bolgesi 2' address from dual union all
select 'TW'country, '5F-6,No. 51, Keelung Rd., Sec.2,' address from dual union all
select 'TW'country, 'Ciyun Road' address from dual union all
select 'UA'country, 'Kulparkivska, 222A' address from dual union all
select 'UA'country, 'Kulparkivska 222A' address from dual union all
select 'US'country, '8000 W FLORISSANT AVE' address from dual union all
select 'US'country, '8600 MEMORIAL PKWY SW' address from dual union all
select 'ZA'country, 'Private Bag 3, WITS' address from dual union all
select 'ZA'country, 'Cnr Alpha and Branch Roads,Driehoek' address from dual
)
)
select country,
       substr ( 
         address, 
         1, 
         case instr ( address, ',' )
           when 0 then length ( address ) 
           else instr ( address, ',' ) - 1
         end
       ) address_1,
       trim ( 
         substr ( 
           address,  
           case instr ( address, ',' )
             when 0 then null
             else instr ( address, ',' ) + 1
           end
         ) 
       ) address_2
from   t1;

COUNTRY    ADDRESS_1                             ADDRESS_2                     
AE         Tawazun Industrial Park               <null>                         
AT         B&R Strasse 1                         <null>                         
AT         Dr.-Auner-Straße 21                   <null>                         
AU         9 Help Street                         Level 4                        
BA         ul. Trg nezavisnosti 5                <null>                         
BA         Poljice bb                            <null>                         
BE         Bargiestraat 2                        <null>                         
BE         PRESIDENT KENNEDYPARK 35              <null>                         
BG         Spectrum Building                     Bequerel Avene                 
BG         bul.Tsarigradsko shose 90             <null>                         
BM         Clarendon House 2 Church Street       <null>                         
BM         22 Victoria Street                    <null>                         
BR         Rua General Bertoldo Klinger          277                            
BR         AV. ORLANDA BERGAMO 1062              <null>                         
CA         4281 Harvester Rd.                    <null>                         
CA         1495 Franklin Street                  <null>                         
CH         Helkenstr 13                          <null>                         
CH         Tschachenstrasse                      <null>                         
CN         No.555 XingShun Road                  <null>                         
CN         1 Aqi Road                            Mapo Town                      
CZ         V Parku 2325/16                       <null>                         
CZ         K Vidouli 15 / Safrankova 1           <null>                         
DE         Landshuter Str. 100                   <null>                         
DE         Blochstr. 1                           <null>                         
DK         Bang og Olufsen Allé 1                <null>                         
DK         Peter Bangsvej 15                     <null>                         
DZ         06 Boulevard Mohamed V                <null>                         
EE         Valga mnt 7a                          <null>                         
EE         Ehitajate tee 5                       <null>                         
EG         Public Free Zone                      Nasr City                      
ES         Avenida John Lennon                   s/n                            
ES         Poligono Malpica                      C/F Oeste                      
FI         Karakaari 7                           <null>                         
FI         Kutomotie 16                          <null>                         
FR         21 Av Du Marechal Foch                <null>                         
FR         45 Rue De Villiers                    <null>                         
GB         Woodhouse Lane                        <null>                         
GB         51 Jardine Crescent                   <null>                         
GG         Third floor                           Royal Bank Place               
GG         Third floor                           Royal Bank Place               
HR         Štoosova 1                            <null>                         
HR         Štoosova 1                            <null>                         
HU         Harrer Pál u. 3-5.                    <null>                         
HU         Damjanich János út 8.                 <null>                         
IE         East Park                             <null>                         
IE         Building 6550                         Avenue 6000                    
IL         30 Hasivim St.                        <null>                         
IL         24 Zarhin Street                      POB 4334                       
IN         #2064                                 SIRI IRIS – 2nd Floor          
IN         Doddakannelli                         Sarjapur Rd.                   
IS         Skulgata 19                           <null>                         
IS         Skúlagötu 19                          <null>                         
IT         Via Remo De Feo                       <null>                         
IT         Via Statale 113                       <null>                         
JP         Tokyo SumitomoTwin Building 27-1      <null>                         
JP         4-16-1 Okada                          <null>                         
KR         150-20                                Gongse-ro                      
KR         199                                   Chongchon-Dong, Bupyong-Gu     
KY         The Grand Pavillion                   West Bay Road                  
KY         The Grand Pavilion                    West Bay Road,                 
LI         Im alten Riet 102                     <null>                         
LI         Im alten Riet 102                     <null>                         
LU         6D                                    Route de Treves                
LU         6D                                    Route de Trèves                
LV         Akme?u iela 72                        <null>                         
LV         Ventspils Augsto tehnoloìiju parks    <null>                         
MA         Batiment B4                           1st Floor, Technopolis         
MA         Lotissement El Menzeh                 <null>                         
ME         Elektrotechnicki fakultet             <null>                         
ME         Elektrotechnicki fakultet             <null>                         
MX         Av. Hermanos Escobar 5756             <null>                         
MX         Ave. Hermanos Escobar 5756            <null>                         
MY         737-1-10                              Kompleks Sri Sg Nibong         
MY         PLO 208 Jalan Cyber 14                <null>                         
NA         2 Jakaranda St                        <null>                         
NL         Science Park Eindhoven 5010           <null>                         
NL         Het Zuiderkruis 53                    <null>                         
NO         Blindern                              <null>                         
NO         Drengsrudhagen 2                      <null>                         
PL         ul. Nowowiejska 15/19                 <null>                         
PL         Al. Jana Kasprowicza 20               <null>                         
PT         Edif                                  Uninova, Suite 28              
PT         Rua Max Grundig 35                    <null>                         
RO         Nojoridului 90                        <null>                         
RO         Strada Thomas Masaryk 19              <null>                         
RU         cheusescu street 5                    <null>                         
RU         5-4/5 Plotnikov per                   <null>                         
SA         King Faisal Street AlKhumra           <null>                         
SA         (* Need Street Address *)             <null>                         
SE         Fabriksgatan 13                       <null>                         
SE         Chalmersplatsen 4                     <null>                         
SG         61                                    Science Park Rd.               
SG         61                                    Science park road              
SI         Reboljeva ulica 1                     <null>                         
SI         Partizanska 12                        <null>                         
SK         Vazovova 5                            <null>                         
SK         Domaniža 380                          <null>                         
TR         Haluk Turksoy Sok                     <null>                         
TR         Organise Sanayi Bolgesi 2             <null>                         
TW         5F-6                                  No. 51, Keelung Rd., Sec.2,    
TW         Ciyun Road                            <null>                         
UA         Kulparkivska                          222A                           
UA         Kulparkivska 222A                     <null>                         
US         8000 W FLORISSANT AVE                 <null>                         
US         8600 MEMORIAL PKWY SW                 <null>                         
ZA         Private Bag 3                         WITS                           
ZA         Cnr Alpha and Branch Roads            Driehoek     


I'm not sure this is a safe rule for all addresses for all countries though. Addresses can come in all sorts of formats; trying to extract them to a standard format is a tough task.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.