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;

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

  1. Kaly says:

    For small text it is ok, but if the string is very long better to use loop

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: