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

Max number in a Varchar column!


Well, to find the maximum number in a VARCHAR2 Column is bit tricky and we can find it in many ways. An example can be to find the maximum number in the Segment1 column in mtl_system_items_b table. Here is one of the best ways to find it:

select max(to_number(segment1))
FROM mtl_system_items_b
where regexp_like(segment1, '^-?[[:digit:],.]*$');