Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

Pl Sql Tips

1- How to split one string using a separator character ?
=> Solution : tested on my Oracle 10G R2 Database

TEST> create or replace type tabchar as table of varchar2(4000);
2 /

Type created.

TEST> create or replace function splittotabchar
2 (pInput IN VARCHAR2,pSeparator IN char := ‘_’) return tabchar
3 pipelined
4 is
5 vPosSep NUMBER;
6 vTemp VARCHAR2(4000);
7 begin
8
9 vTemp := pInput;
10 vPosSep := instr(vTemp,pSeparator);
11
12 while (vPosSep != 0)
13 loop
14 pipe row (substr(vTemp,1,vPosSep-1));
15 vTemp := SUBSTR(vTemp,vPosSep+1);
16 vPosSep := instr(vTemp,pSeparator);
17 end loop;
18
19 pipe row(vTemp);
20 end;
21 /

Function created.

TEST> select * from table(splittotabchar(‘abc_def_ghI’));

COLUMN_VALUE
————————————————————————————————————————————————————————————
abc
def
ghI

TEST>

Advertisements

2 Comments »

  1. Hi Wissem,

    This is my 1st comment to your site. 🙂

    I did use the same method on Oracle 9i and the process became quite fast. The dynamic table becomes a problem if you’ll run it many times in a short period. The process just crashed.

    My second trial was few years later on 10g and this time the process didn’t crash, but it was very slow.

    This method is prefect was a simple process, but for loops I would prefer a real or a temporary table.

    Take care!
    Tenno

    Comment by Tenno — July 1, 2010 @ 2:44 pm | Reply

    • Nice to see you here :p and hope getting more comments from you 🙂
      Pl sql pipelined functions are really useful if there is a select from a source other than a table, maybe I Will choose you temporary table guess ;).

      Comment by orawiss — July 1, 2010 @ 3:16 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: