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 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.