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;
Advertisements

How to make parameters optional in Discoverer?


When you create a parameter for your discoverer report, sometimes we need to make the parameter as optional. You cannot make parameters as optional in Discoverer Desktop. This feature is only available in Discoverer Plus.

Discoverer Parameter in desktop

Discoverer Parameter in plus