Split Function for Oracle

create type ty_str_split as table of varchar2(100)
/

create or replace function str_split_as_table (p_str   in varchar2
                                              ,p_delim in varchar2 default ',') 
return ty_str_split pipelined as

  v_tab    ty_str_split;
begin
  v_tab := str_split(p_str,p_delim);
  
  for i in 1..v_tab.last loop
    pipe row(v_tab(i));
  end loop;
  return;
end;;
/

create or replace function str_split  (p_str   in varchar2
                                     ,p_delim in varchar2 default ',') 
return ty_str_split as
  v_str      varchar2(32767);
  v_fields   pls_integer;
  v_substr   varchar2(32767);
  v_return   ty_str_split := ty_str_split();
begin
  v_str := p_delim||trim(p_delim from p_str)||p_delim;
  v_fields := length(v_str) - length(replace(v_str,p_delim,'')) - 1;
  v_return.extend(v_fields);
  
  for i in 1..v_fields loop
    v_substr := substr(v_str
                      ,instr(v_str,p_delim,1,i)+1  
                      ,instr(v_str,p_delim,1,i+1) - instr(v_str,p_delim,1,i) -1);
    v_return(i) := v_substr;
  end loop;
  return v_return;
end;;
/        

select *
from   table(str_split_as_table('asd,456,trfgh,4567'));

select str_split('asd,456,trfgh,4567') from dual;
Advertisements

2 thoughts on “Split Function for Oracle

  1. hi I was luck to seek your website in digg
    your topic is marvelous
    I get a lot in your website really thank your very much
    btw the theme of you blog is really exceptional
    where can find it

    1. Hey Bet,

      Thank you for your comment. It’s great to see that you enjoy visiting my website and better yet see that the information I share here has being useful to you.

      Oh, I like this theme too. I found in the WordPress Themes Gallery and it’s called Motion.

      Hope to keep having you around.

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