Friday, 30 May 2014

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;

1 comment:

  1. I am Sridevi Koduru, Senior Oracle Apps Trainer With 8 Yrs Exp on Oracle Apps and 13 Yrs IT Exp Providing Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.

    Contact for (One to One Personal Online Training) on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Financial for Indian Localization, Oracle Apps SCM, Oracle Apps HRMS, SQL, PL/SQL and D2K at training@oracleappstechnical.com or sridevikoduru@oracleappstechnical.com | +91-9581017828 | http://www.oracleappstechnical.com

    Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/

    ReplyDelete