How to get value from comma separated string in oracle

Hi Friends,

Today I'm here with a new program. The problem is that we have given a string with comma separated values i.e 'wave,the,world,oracle,program,' and we have to extract values from the string.

So the input value will be,
Input : 'wave,the,world,oracle,program,'

and output will be like this,
Output :
wave
the
world
oracle
program

Let's start, we are going to use following Oracle builtin function in the program.







length()
translate()
substr()
instr()

So first we will understand the working of these functions.

• length() : The Oracle length() function returns the length of the specified string.

          i.e. select length('wave the world') from dual;
          Result : 14

• translate() : The Oracle translate() function replaces a sequence of character in a string with another set of characters. It replaces a single character at a time. i.e it will replace the first character in the string_to_replace with the first character in the replacement_string. The it will replace the second character and so on.

          i.e select translate('wave the world','e ','AS') from dual;
          Result : wavASthASworld

• substr() : The Oralce substr() function allows you to extract a substring from a string.

          i.e select substr('wave the world',0,4) from dual;

          Result : wave

• instr() : The function returns the location of a substring in a string.

          i.e select instr('wave the world','the') from dual;

          Result : 6

Now we will create a pl/sql block to do the job by using all these functions.

declare
    comma varchar2(20);
    st varchar2(50):='wave,the,world,oracle,program,';
    st_temp varchar2(20);
begin
    comma:=length(st) -length(translate(st,'A,','A'));
    for i in 0.. comma - 1 loop
        st_temp := substr(st,0,instr(st,',')-1);
        dbms_output.put_line(st_temp);
        st:= substr(st,instr(st,',') + 1);
    end loop;
end;

When will you run this pl/sql block you will get the desired output.

Output :

wave
the
world
oracle
program

How this code works? 

First we count the no of comm in the string and run a for loop. Everytime we cut the string that is printed and loop contine run till the all values from string printed.

Share your thoughts in comment section.

Enjoy :)