Home > SPLIT Function > Customize SPLIT Function written in Oracle (PLSQL)

Customize SPLIT Function written in Oracle (PLSQL)

A common task when selecting data from a database is to take a set of values a query returns and format it as a comma delimited list. Another task that’s almost as common is the need to do the reverse take a comma delimited list of values in a single string and use it as a table of values.

To serve this purpose I preferred following function to Split string.

Split Function:

create or replace function split(
  p_InputString   varchar2,
  p_Position      number,
  p_Delimiter     varchar2
return varchar2
   v_list varchar2(32767) := p_Delimiter || p_InputString;
   v_start_position number;
   v_end_position number;
   v_start_position := instr(v_list, p_Delimiter, 1, p_Position);
   if v_start_position > 0 then
      v_end_position := instr( v_list, p_Delimiter, 1, p_Position + 1);
         if v_end_position = 0 then
            v_end_position := length(v_list) + 1;
         end if;
         return(substr(v_list, v_start_position + 1, v_end_position - v_start_position - 1));
         return NULL;
   end if;
end split;
Show Error

Output 1:

select split('Tamim Khan',1,' ') from dual;

Output 2:

select split('Tamim Khan',2,' ') from dual;


Output 3:

select split('Dhanmondi,Dhaka',1,',') from dual;                        

Output 4:

select split('Dhanmondi,Dhaka',2,',') from dual;                        
Categories: SPLIT Function Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

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: