Say to split the below string into 4 variables based on the separator :
l_string : = ‘AAAA:BBBB:CCCC:DDDD’;
l_string : = ‘AAAA:BBBB:CCCC:DDDD’;
- l_var1:= ‘AAAA’;
- l_var2:= ’BBBB’;
- l_var3:= ‘CCCC’;
- l_var4:= ’DDDD’;
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;
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.
ReplyDeleteContact 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/