Monday, October 15, 2018

Get multiple substring starting with special characters in col

Today my project is trying to load the Window AD Service Account data into Oracle Database, the Service Account data was captured by one script which has Account Name/description/Membership info, the Membership col is having data like this:

CN=xxxx,OU=....,DC=... CN=yyyy.

I will need to get all the substring after the CN= and character ',', which can be one or any number values,

Here is what I did:

1. Create a function to get all the member into one string like xxxx:yyyy

create or replace function get_ad_member(member_string in varchar2)
return varchar2 
is
out_member varchar2(4000);
begin
select listagg(member,':') within group(order by member) mm into out_member from 
(
select replace(REGEXP_SUBSTR(q,'[^,]+',1,1),'CN=','') member from ( 
select * from (
select trim(regexp_substr(member_string,'[^,]+[^ ]+', 1,level) ) as q 
from dual
connect by regexp_substr(member_string,'[^,]+|[^ ]+', 1, level) is not null
order by level
)
where q like '%CN=%'
)
);
return out_member;
end;


Note: the reason I am using '[^,]+[^ ]+' is because before the CN=, sometime it has ',', sometime it's s ' '. 
Based on your actual situation, you can adjust the format of your own

2, in the sql, use the function before.