How to split a string in oracle into variables based on the separator?
January 31, 2013 1 Comment
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;