1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| create or replace FUNCTION FormateAdresse(IN_ADRSCE IN VARCHAR2)
RETURN VARCHAR2
IS V_RET VARCHAR2(500);
BEGIN
WITH CTE AS(
select distinct regexp_substr(replace(trim(replace(V_ret,' ',' ')),' ',' '),'[^('||chr(10)||')]+',1,level) as lines, rownum as ordre from dual
connect by regexp_substr(replace(trim(replace(V_ret,' ',' ')),' ',' '),'[^('||chr(10)||')]+',1,level) is not null
order by ordre
),
cte2 as(
select rownum,lines,
case when length(lines) > 38
then trim(regexp_replace(lines,'[^,]',chr(10),instr(lines, ' ',-1,1),1))
else trim(lines)
end as modif
from cte
)
select listagg(modif,chr(10))WITHIN GROUP (ORDER BY rownum) into V_ret from cte2;
return V_ret;
END FormateAdresse; |