How to split a string in oracle into variables based on the separator?

Say to split the below string into 4 variables based on the separator  :

l_string : = ‘AAAA:BBBB:CCCC:DDDD’;

• l_var1:= ‘AAAA’;
• l_var2:= ’BBBB’;
• l_var3:= ‘CCCC’;
• l_var4:= ’DDDD’;

 

Script

DECLARE

  l_string VARCHAR2(100) := 'AAAA:BBBB:CCCC:DDDD';

  l_var1   VARCHAR2(240);

  l_var2   VARCHAR2(240);

  l_var3   VARCHAR2(240);

  l_var4   VARCHAR2(240);

BEGIN

  SELECT trim('"'

  FROM regexp_substr(l_string,'".*?"|[^:]+',1,1)) Col1,

    trim('"'

  FROM regexp_substr(l_string,'".*?"|[^:]+',1,2)) Col2,

    trim('"'

  FROM regexp_substr(l_string,'".*?"|[^:]+',1,3)) Col3,

    trim('"'

  FROM regexp_substr(l_string,'".*?"|[^:]+',1,4)) Col4

  INTO l_var1 ,

    l_var2 ,

    l_var3 ,

    l_var4

  FROM dual ;

  dbms_output.put_line('Var1 : ' ||l_var1);

  dbms_output.put_line('Var2 : ' ||l_var2);

  dbms_output.put_line('Var3 : ' ||l_var3);

  dbms_output.put_line('Var4 : ' ||l_var4);

END;