D
Proposal for implementation.Extend regular expression templates from all functions to a separate table:create table normalizerconf (tabname, colname, ty, splitpatt, rmpatt) as
select 'data1', 'col1', 'phone', '(.*?)((,\s+)|($))', '\D+' from dual
/
Then the job request will look like that. https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c3de7f153cc067c406df5f8db092e4b9 create table data1 (id, col1) as
select 1, '123/456789, 032-156789' from dual
/
with
function getphonelist (
col varchar2, splitpatt varchar2, rmpatt varchar2) return varchar2 is
begin
return NormalizePhone (col, splitpatt, rmpatt).join();
end;
select d.col1, getphonelist (col1, splitpatt, rmpatt) result
from data1 d
cross join normalizerconf c
where c.tabname = 'data1' and colname = 'col1'
/
COL1 RESULT
123/456789, 032-156789 123456789; 032156789
Establish a basic user type, for each data type, its inherited type:create or replace type Normalizer as object (
tokens tokenList,
member procedure split (str varchar2, pattern char), -- return tokenList,
not instantiable member procedure normalize (rmpatt char),
member function join (delimiter char := '; ') return varchar2
) not instantiable not final
/
create or replace type NormalizePhone under Normalizer (
constructor function NormalizePhone (
str varchar2, splitpatt char, rmpatt char) return self as result,
overriding member procedure normalize (rmpatt char) -- return tokenList,
) instantiable final
/
Example of the types of implementation:create or replace type body Normalizer as
member procedure split (str varchar2, pattern char) is
s varchar2(96);
c int := 0;
begin
<<split>> loop c := c + 1;
s := regexp_substr (str, pattern, 1, c, null, 1);
exit split when s is null;
self.tokens.extend;
self.tokens(tokens.last) := s;
end loop;
end split;
member function join (delimiter char := '; ') return varchar2 is
ret varchar2 (32767);
begin
for i in 1..self.tokens.count loop
ret := ret||tokens(i)||delimiter; end loop;
return rtrim (ret, delimiter);
end join;
end;
/
create or replace type body NormalizePhone as
constructor function NormalizePhone (
str varchar2, splitpatt char, rmpatt char) return self as result is
begin
self.tokens := tokenList();
self.split (str, splitpatt);
normalize (rmpatt);
return;
end;
overriding member procedure normalize (rmpatt char) is
begin
for i in 1..self.tokens.count loop
self.tokens(i) := regexp_replace (self.tokens(i), rmpatt);
end loop;
end;
end;
/
