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

August 24, 2010

Pl Sql Tips

Filed under: Uncategorized — orawiss @ 3:52 pm

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

Leave a Comment »

No comments yet.

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: