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;
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:
Post a Comment