Seperate value of a specific column with "," delimiter in Oracle using "Regular Expression"
Such an approach makes sure you don't get too many duplicates as your query does (so you removed them with a distinct in line #4; without it, you'd get 12 rows as a result):
SQL&gt; with customer_info (cust_id, cust_name, cust_address) as
  2    (select 1, 'A', 'Tehran,Tehran' from dual union all
  3     select 2, 'B', 'Rasht,Tehran'  from dual union all
  4     select 3, 'C', 'Minsk,Tehran'  from dual
  5    )
  6  select --distinct      --&gt; use DISTINCT to get result you want
  7         cust_id,
  8         cust_name,
  9         regexp_substr(cust_address, '[^,]+', 1, column_value) cust_address
 10  from customer_info cross join
 11    table(cast(multiset(select level from dual
 12                        connect by level &lt;= regexp_count(cust_address, ',') + 1
 13                       ) as sys.odcinumberlist))
 14  order by cust_id;

   CUST_ID C CUST_ADDRESS
---------- - ----------------------------------------------------
         1 A Tehran
         1 A Tehran
         2 B Rasht
         2 B Tehran
         3 C Minsk
         3 C Tehran

6 rows selected.

SQL&gt;

However, as you want to have only one 1-A-Tehran combination, distinct in line #6 has to be uncommented.