Hi all,
I would like to make a function which passing as parameter a string
and return the corresponding regular expression in order to be matched. (The initial format is saved in a table with default values and it is saved by the customer)
Moreover, the initial format contains only 'A' and '-'
example 1 : passing AA-AAAA-A gives output '^[^_\W]{2}-[^_\W]{4}-[^_\W]$'
example 2 : passing -AA-AAAA-A gives output '^-[^_\W]{2}-[^_\W]{4}-[^_\W]$'
example 3 : passing AAAAA gives output '^-[^_\W]{5}$'
example 4 : passing A- gives output '^[^_\W]-$'
FUNCTION make_regex (test_str IN varchar2 )
RETURN varchar2
IS
v_regex varchar2(50);
BEGIN
SELECT '^' || REGEX || '$'
INTO v_regex
FROM (
SELECT LISTAGG(LEN, '-') WITHIN GROUP (ORDER BY ROWNUM) AS REGEX
FROM (
SELECT '[^_\W]' || DECODE(LENGTH(STR),1,'','{'||LENGTH(STR)||'}') AS LEN, ROWNUM FROM (
select regexp_substr(test_str,'[^-]+', 1, level) as STR
from dual
connect by regexp_substr(test_str, '[^-]+', 1, level) is not null
)
))
);
return v_regex;
END make_regex;
Could you help me please do it ?
Only the first example works as expected.