1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
| declare
tata varchar2(200);
rue varchar2(20) := 'RUE';
ZI varchar2(20) := 'ZI';
--CC varchar2(20) := 'CEDEX';
CC varchar2(20) := '';
nom_rue varchar2(200);
nom_zi varchar2(200);
nom_cc varchar2(200);
begin
WITH test AS (
--SELECT 'RUE des jonquilles ZI sud CC leclerc' AS ad FROM dual-- union ALL
--SELECT 'RUE des jonquilles ' AS ad FROM dual union ALL
--SELECT 'RUE des jonquilles ZI sud' AS ad FROM dual --union ALL
SELECT 'RUE des jonquilles ZI sud CEDEX PARIS XV' AS ad FROM dual
--SELECT 'CC leclerc RUE des jonquilles' AS ad FROM dual
)
SELECT test.ad,
case when RUE is not null and ZI is not null and CC is not null
then REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(test.ad, '^.*'|| RUE||'.', ' '), ZI||'.*$', ''), CC ||'.*$', '')
when RUE is not null and ZI is not null and CC is null
then REGEXP_REPLACE(REGEXP_REPLACE(test.ad, '^.*'|| RUE||'.', ' '), ZI||'.*$', '')
end "RUE",
case when RUE is not null and ZI is not null and CC is not null
then REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(test.ad, '^.*'||ZI||'.', ' '), RUE||'.*$', ''), CC||'.*$', '')
when RUE is not null and ZI is not null and CC is null
then REGEXP_REPLACE(REGEXP_REPLACE(test.ad, '^.*'||ZI||'.', ' '), RUE||'.*$', '')
end "ZI",
case when RUE is not null and ZI is not null and CC is not null
then REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(test.ad, '^.*'||CC||'.', ' '), RUE||'.*$', ''), ZI||'.*$', '')
when RUE is not null and ZI is not null and CC is null
then REGEXP_REPLACE(REGEXP_REPLACE(test.ad, RUE||'.*$', ''), ZI||'.*$', '')
end "CC"
into tata, nom_rue, nom_zi, nom_cc
FROM test;
DBMS_OUTPUT.PUT_LINE(nom_rue||' : '|| nom_zi||' : '|| nom_cc);
end; |
Partager