Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

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>