Split comma separated values into rows Oracle database

Most often we receive comma separated text data that we need to convert to rows. 
To do this with SQL we can use several method.

Method : 1 >> Using SQL Query

select regexp_substr( 

 ltrim(rtrim(REGEXP_REPLACE(REPLACE('382,   Al-amin Road,South Paikpara, Kollaynpur, Mirpur,Dhaka',',',', ') , ' {2,}', ' '))), '[^,]+', 1, level) My_Add_Line

from dual

 connect BY regexp_substr( 

 ltrim(rtrim(REGEXP_REPLACE(REPLACE('382,   Al-amin Road,South Paikpara, Kollaynpur, Mirpur,Dhaka',',',', ') , ' {2,}', ' '))), '[^,]+', 1, level)   is not null;

 

 

 Method: 2 >> Using a WITH clause

  with add_tab as (

  select  ltrim(rtrim(REGEXP_REPLACE(REPLACE('345,   Al-amin Road,South Paikpara, Kollaynpur, Mirpur,Dhaka',',',', ') , ' {2,}', ' '))) my_str from dual

)

  select ltrim(rtrim(regexp_substr (

            my_str,

           '[^,]+',

           1,

           level

         ))) My_Add_Line

  from   add_tab

  connect by level <= 

    length ( my_str ) - length ( replace ( my_str, ',' ) ) + 1;

Method : 3 >> Using Procedure With dbms_output

DECLARE

p_str VARCHAR2(2000) := '345,  Al-amin Road  ,South Paikpara, Kollaynpur, Mirpur,Dhaka';

BEGIN

FOR i IN (

            select regexp_substr( ltrim(rtrim(REGEXP_REPLACE(REPLACE(p_str,',',', ') , ' {2,}', ' '))), '[^,]+', 1, level) My_Add_Line

from dual

 connect BY regexp_substr( 

 ltrim(rtrim(REGEXP_REPLACE(REPLACE(p_str,',',', ') , ' {2,}', ' '))), '[^,]+', 1, level)   is not null)

    LOOP

dbms_output.Put_line(i.My_Add_Line);

    END LOOP;

END;