Adsense Ad

Sunday, 22 March 2020

Oracle PL/SQL: Split Comma Separated String (CSV)

I developed a function in Oracle PL/SQL. By which we can split comma separated string and can pick string by just defining its position.

Following is the code of the function:

Create Or Replace Function Split_Csv_Text(Text Varchar2, Position Number)
  Return Varchar2 Is
  Res Varchar2(4000);
Begin
  Res := Regexp_Substr(Text, '([^,]*)(,|$)', 1, Position, Null, 1);
  Return Res;
Exception
  When Others Then
    Res := Sqlerrm;
    Return Res;
End;


Another function in Oracle PL/SQL, which is also able to split string but in this function we can defined any separator which its not only dependent on "," comma only.

Create Or Replace Function Split_Text_Deli(p_String  In Varchar2,
                                           p_Element In Number,
                                           p_Delim   In Varchar2 Default ',')
  Return Varchar2 As
  v_String Varchar2(32767) := p_Delim || p_String || p_Delim;
Begin
  Return Substr(v_String,
                Instr(v_String, p_Delim, 1, p_Element) + Length(p_Delim),
                Instr(v_String, p_Delim, 1, p_Element + 1) -
                Instr(v_String, p_Delim, 1, p_Element) - Length(p_Delim));
End Split_Text_Deli;

No comments: